Remover zeros à esquerda no Excel

Publicado em Tecnologia, em 21/12/2007 às 13h19 (atualizado em 02/03/2015 às 21h19)
Marcadores: dicas, Excel, planilhas e material didático

Dentre os diversos ajustes necessários aos dados de uma planilha, estão a adição e a remoção de zeros à esquerda, normalmente em códigos ou números de série de produtos.

Quando li o comentário de Daniel perguntando como remover os zeros à esquerda no MS-Excel^®, pensei que existisse alguma função (ou combinação de funções) para este fim.

Infelizmente, não encontrei. Nem no Openoffice.org Calc, que gosto tanto…

Sendo assim, combinei algumas funções para obter o mesmo resultado…

Acrescentar zeros à esquerda de uma célula completando-a até um determinado comprimento:

Se for um número (como transformar 404 em 00404), obtém-se esse resultado por meio de formatação:
1. clique no menu Formatar > Células;
2. clique na aba Número;
3. clique na opção de formato Personalizado; e
4. preencha com zeros à esquerda até o tamanho desejado da célula (no caso, cinco zeros).
Se for um texto (como transformar 40A em 0040A):
1. acrescentam-se zeros à esquerda na quantidade desejada de caracteres (no caso, cinco zeros); e
2. seleciona-se o tamanho desejado (cinco caracteres) contando a partir da direita:
  =DIREITA(CONCATENAR(REPT("0";5);A1);5)

Remover zeros à esquerda de uma célula:

Se for um número, é uma mera questão de formatação, similar à explicada acima;
Se for um texto (como transformar 00980YUT0 em 980YUT0), é necessário usar alguns artifícios:
1. remova todos os zeros;
2. procure pelo primeiro caractere no conteúdo original da célula; e
3. extraia o conteúdo da célula, do ponto encontrado até o seu fim (no caso, 9 caracteres):
  Excel
  =EXT.TEXTO(A1;PROCURAR(ESQUERDA(SUBSTITUIR(A1;"0";"");1);A1);9)
  Openoffice.org Calc
  =MEIO(A1;PROCURAR(ESQUERDA(SUBSTITUIR(A1;"0";"");1);A1);9)

Fácil, não?

Atualização: Acrescentada versão para Openoffice.org Calc.

45 comentários sobre “Remover zeros à esquerda no Excel”

Rafael Montera em 22/07/2008 às 11h45 disse:

Gostaria de saber como faço para substituir o ponto de interrogação (“?”) de uma planilha de excel sem que ele substitua todos os caracteres..

Responder ↓
- José Luís em 22/07/2008 às 12h07 disse:
  
  Rafael,
  
  O caractere til (~) antes dos caracteres curinga, interrogação (“?”) ou asterisco (“*”), força-os a assumir seu valor normal.
  
  Portanto, experimente procurar por “~?” (sem as aspas).
  
  Responder ↓
Elaine em 02/11/2008 às 15h39 disse:

Um dia fiz um teste para entrar numa empresa e fui surpreendida por causa desse problema. Não soube colocar o zero a frente do número. por exemplo “001”.
Agora com seu exercício não serei surpeendida mais.
Tô aprendendo muito com vc.
Valeu!!!!!!

Responder ↓
José Augusto Gomes da Cruz em 08/12/2008 às 6h47 disse:

Olá Amigo,

Estou fazendo uma planilha e preciso que a fórmula encontre dentro de uma célula onde encontra-se uma frase, fazer com que ela encontre apenas uma palavra e se tiver essa palavra mover para outro local. Não sei se consegui explicar direito.

Exemplo:

A1 contem o texto “O marido dela é feio”, eu estou precisando de uma fórmula que encontre na tabela apenas a palavra: “marido” e se tiver a palavra dentro da célula pegar a linha inteira e jogar para outra planilha, vc acha que isso é possível?

Obrigado desde já!!!

Responder ↓
- José Luís em 08/12/2008 às 10h20 disse:
  
  José Augusto, interessante a sua pergunta! Ainda não sei, mas vou pesquisar.
  
  Responder ↓
  - José Luís em 08/12/2008 às 13h41 disse:
    Pronto! As funções PROCURAR() e PESQUISAR() podem resolver o que você deseja. Sugiro a segunda, porque ela não diferencia maiúsculas e minúsculas.
    
    Suponha que:
    
    a coluna A contém os textos onde a pesquisa ocorrerá;
    
    a coluna B exibirá o resultado; e
    
    o texto a ser procurado está em D1.
    
    A fórmula =SE(PESQUISAR($D$1;A1;1)<>0; ""; A1) cópia a célula se o texto for encontrado.
    
    Porém, se o texto não for encontrado, será exibida uma mensagem de erro. Para evitá-la, sugiro fazer o teste com a função ÉERROS():
    
    =SE(ÉERROS(PESQUISAR($D$1;A1;1)<>0); ""; A1)
    
    Boa sorte!
    Responder ↓
Luciana em 05/05/2009 às 12h31 disse:

Essas dicas salvaram meu dia.
Muito obrigada!

Responder ↓
Junior em 01/11/2009 às 16h37 disse:

José Luis,

Estou tentado modificar uma tabela com numeros IPs por exemplo:

na tabela está 10.98.6.244

gostaria de completar os octetos com três casas onde não tiver completar com zeros:

010.098.006.244, como posso fazer??

Grato.

Responder ↓
PCH em 17/12/2009 às 8h54 disse:

Ola,

meu problema é na direção contraria, como formatar uma célula com zeros automáticos à direita. Formatei uma céluna como personalizada com o segunte formato 000000000000000, ao inserir numeros nesssa célula com menos de 15 dígitos fica assim 000000123456789. mas quero o resultado assim 123456789000000, teria como?

Responder ↓
- EDy em 05/05/2010 às 8h13 disse:
  
  Sim tem como voce pode fazer de duas formas
  1 forma
  =CONCATENAR(REPT(0;6-NÚM.CARACT(B6));B6)
  Exemplo 12345 (o codigo deve ter 10 digitos)
  Resultado 0000012345
  
  2 forma
  =CONCATENAR(B6;REPT(0;6-NÚM.CARACT(B6)))
  Exemplo 12345 (o codigo deve ter 10 digitos)
  Resultado 1234500000
  
  Tem como usar as funcoes esquerda e direita mas assim e mais facil e simples
  
  Responder ↓
  - EDy em 05/05/2010 às 8h15 disse:
    
    So corrigindo a formula anterior para 10 digitos voces vai mudar da seguinte forma
    
    =CONCATENAR(REPT(0;6-NÚM.CARACT(B6));B6)
    Exemplo 12345 (o codigo deve ter 10 digitos)
    Resultado 0000012345
    
    Ficara assim
    =CONCATENAR(REPT(0;10-NÚM.CARACT(B6));B6)
    Exemplo 12345 (o codigo deve ter 10 digitos)
    Resultado 0000012345
    
    =CONCATENAR(B6;REPT(0;10-NÚM.CARACT(B6)))
    Exemplo 12345 (o codigo deve ter 10 digitos)
    Resultado 0000012345
    
    Responder ↓
    - ellen em 24/11/2020 às 14h33 disse:
      
      Como retirar os 0 da frente do número?
      
      Responder ↓
FelixLoco em 07/07/2010 às 9h32 disse:

Perfeito!!!!

Responder ↓
Thiago em 01/10/2010 às 15h07 disse:

olá!

ficaria muito grato se pudessem me ajudar…

estou tentando fazer com que apareça datas como 10/10/10 na barra de fórmula sem que eu precise digitar as barras na célula.
até consigo que esta formatação apareça na planilha, usando ##”/”##”/”## como formatação. mas isso está fazendo com que a data fique apenas como texto na barra de fórmula e está complicando na hora de usar a Classificação.

muito obrigado.

Responder ↓
- José Luís em 01/10/2010 às 17h50 disse:
  Thiago,
  
  Acho que você tem uma boa intenção (poupar digitação em grandes volumes de dados). Mas o resultado pode se mostrar um pouco confuso, tornando a relação custo/benefício pouco vantajosa.
  
  De qualquer forma, você começou na direção certa: usa
  a formatação para acrescentar as barras de data. Contudo, o valor dessa célula não serve para cálculos, porque ainda é um texto.
  
  Para solucionar esse problema, eu usaria uma coluna auxiliar (oculta), com uma fórmula baseada nas funções:
  - EXT.TEXTO() – que extrai caracteres de um texto, no caso, da célula contendo a data digitada; e
  - DATA() – que monta uma data baseada em valores de ano, mês e dia informados.
  Considerando que a célula A1 contém a data digitada, a fórmula seria algo como:
  =DATA(EXT.TEXTO(A1;5;2); EXT.TEXTO(A1;3;2); EXT.TEXTO(A1;1;2))
  Responder ↓
  - Thiago em 05/10/2010 às 1h13 disse:
    
    Muito obrigado pela dica, José!
    
    Responder ↓
Ronaldo em 02/01/2012 às 11h05 disse:

Olá, testei o teu post e funcionou em partes, digo em partes pelo motivo de, os 0 apenas aparecerem na visualização e não realmente ser adicionado o 0 na célula.

Há alguma forma que realmente inclua o 0 na célula?

Obrigado!

Responder ↓
- José Luís em 02/01/2012 às 12h58 disse:
  
  Olá, Ronaldo,
  
  A proposta é adicionar zeros à esquerda. Como, à esquerda da vírgula, eles são dispensáveis, não precisam ser adicionados realmente…
  
  De qualquer forma, as fórmulas sugeridas para texto alteram geram um novo valor. Como ele terá os zeros à esquerda, você pode usá-los (desde que, sempre no formato texto, obviamente).
  
  Responder ↓

Boa noite ,
Gostaria de saber se tem alguma forma , no excel por macro ou quelaquer outra formula que eu possa fazer uma soma se baseando so por partes de uma frase .exemplo.

conta prax 2005 – 10
conta 2005 – 20
conta prax 2010 – 30

eu gostaria de so contabilizar contas prax .
total seria 40

como eu conseguiria selecionar so o nome prax ??

Obrigado

Responder ↓

Leonardo, certamente é possível resolver seu problema com macros. Mas, por que usar macros, se podemos resolver o problema usando alguns artifícios simples?

Considere uma planilha com três coluna (A, B e C). A coluna A terá os nomes das contas, a coluna B, o valor digitado pelo usuário e a coluna C, por meio de uma combinação de funções, receberá apenas os valores que deverão ser considerados. O somatório, apesar de ficar na coluna B, corresponderá aos valores da coluna C.

A	B	C
Conta Prax 2005	10	=SE( NÃO( ÉERRO( PESQUISAR( "prax"; A1 ) ) ); B1; 0 )
Conta 2005	20	=SE( NÃO( ÉERRO( PESQUISAR( "prax"; A2 ) ) ); B2; 0 )
Conta Prax 2010	30	=SE( NÃO( ÉERRO( PESQUISAR( "prax"; A3 ) ) ); B3; 0 )
Total	=SOMA( C1:C3 )

Sugiro que a última coluna fique oculta, afinal, um mágico não revela seus truques…

Responder ↓

glaucia em 25/01/2012 às 14h20 disse:

Olá!
Estou com problemas na hora
em que vou digitar numeros na planilha.. quando termino de digitar e dou enter o numero se transforma em formula, e eu não sei tirar essa formula.

me ajudem, por favor.

Obrigada..

Responder ↓
- José Luís em 26/01/2012 às 15h10 disse:
  
  Glaucia, sua descrição não deixou muito claro, mas provavelmente o número está formatado como notação científica.
  
  Experimente mudar a formatação para “Número”.
  
  Responder ↓
  - glaucia em 26/01/2012 às 16h23 disse:
    
    Obrigada Jose Luís!
    É isso mesmo que vc me falou.. o meu Excel estava configurado como notação científica (geral) e não numeros..
    Daí foi só mudar p numeros ele voutou ao normal.
    =)
    Bjos
    
    Responder ↓
jenilson em 25/02/2012 às 14h56 disse:

Boa Tarde!!

gostaria de uma ajuda para a formula concatenar.

tenho um aplanilha no Excel dessa forma abaixo

0000 253060 180212 0730 0001

gostaria que a mesma ficasse desse jeito mantendo o Nº 0

000025306018021207300001

Abraços, fico no aguardo.

Responder ↓
- José Luís em 27/02/2012 às 12h04 disse:
  
  Jenilson, não entendi direito sua dúvida.
  
  Basicamente, concatenamos uma string composta de zeros à esquerda da string contendo o número a ser representado. Em seguida, extraímos uma substring com o tamanho desejado, composta do número com zeros à esquerda.
  
  Dê uma outra lida na dica do post. Se sua dúvida é outra, por favor, dê mais detalhes.
  
  Responder ↓
  - jenilson em 27/02/2012 às 22h45 disse:
    
    obrigado!! agora entendi.. Deu Certo
    
    Responder ↓
ary tx em 04/04/2013 às 9h11 disse:

Estou trabalhando em uma planilha grande, que importa dados para outra planilha, não consigo solucionar uma questão… ref: 05539500004
quando importa, esse primeiro “0” some, ficando 5539500004, com isso não importo, nome, valores e outros que estão vinculados a referência original.

Responder ↓
- José Luís em 04/04/2013 às 14h02 disse:
  
  Ary,
  
  Como é essa “importação”?
  
  Se for a partir de um arquivo .CSV, por exemplo, você pode indicar, durante a importação, as colunas que devem ser tratadas como texto puro.
  
  Se ambas as planilhas estiverem no mesmo arquivo, experimente formatar a(s) célula(s) de destino como texto.
  
  Depois diga o resultado, ok?
  
  Responder ↓
Renan em 11/03/2015 às 12h42 disse:

José,

Tenho o seguinte número 126,716 e quero trabalhar apenas com o número 126. Qual formula no excel ignora os números à direita da vírgula?

Responder ↓
- José Luís em 11/03/2015 às 14h25 disse:
  
  Renan, experimente a função INT()…
  
  Responder ↓
  - Renan em 11/03/2015 às 16h07 disse:
    
    José, Muito Obrigado.
    
    Responder ↓
Raphael Martins em 11/11/2015 às 9h43 disse:

Simples e eficiente, um poema! Obrigado Amigo!

Responder ↓
- José Luís em 11/11/2015 às 10h54 disse:
  
  Obrigado, Raphael!
  
  Seja bem-vindo e sinta-se à vontade para pesquisar mais pelo site, espero que encontre mais coisas que o agradem!
  
  Responder ↓
paulo em 08/01/2016 às 9h43 disse:

remover apena o primeiro zero á esquerda 008:00:00 excel de modo a que fique 08:00:00? alguém me consegue ajudar?

Responder ↓
- José Luís em 08/01/2016 às 11h16 disse:
  Caro Paulo,
  
  Em primeiro lugar, seja bem-vindo!
  
  Em segundo, tentei digitar 008:00:00 no Calc e ele automaticamente ignorou o zero excedente mais à esquerda, considerando apenas o horário esperado (08:00:00). Creio que, por alguma razão, a célula onde está tentando digitar o horário está configurada para receber o valor como texto.
  
  Você pode verificar se é este o caso por alguns indícios:
  - o conteúdo da célula em formato texto normalmente fica alinhado à esquerda; ou
  - ao exibir a formatação da célula, vá na aba “Números” e verifique se está selecionada a categoria “Texto”.
  Se for este o caso, experimente mudar a categoria da formatação para “Hora”.
  
  Depois informe se funcionou, ok?
  Responder ↓
  - paulo em 08/01/2016 às 11h24 disse:
    
    Obrigado pelas boas-vindas, mas o problema persiste quer esteja formatado as celulas em texto ou em hora.
    
    Isto acontece pois é um excel oriundo de outro programa de picagem de ponto e vem nesse formato de hhh:mm:ss
    
    O problema é que eu ao queria colocar as celulas no formato hh:mm:ss e não no formato que fica que é hhh:mm:ss.
    
    Responder ↓
    - José Luís em 08/01/2016 às 12h09 disse:
      
      Ok, entendi.
      
      No momento, não disponho do Excel para testar, mas listarei os passos usados no LibreOffice Calc, deve ajudar.
      
      selecione a(s) célula(s) a ser(em) formatada(s);
      
      acesse a formatação de células pelo menu Formatar > Células… ou pelo menu de contexto, opção Formatar células… (usando o botão direito do mouse/rato);
      
      na janela Formatar células, selecione a aba Números;
      
      selecione a categoria Hora; e
      
      selecione o formato desejado.
      
      Veja os passos na animação a seguir:
      
      Diga-me se funcionou, ok?
      
      Responder ↓
      - paulo em 08/01/2016 às 12h36 disse:
        
        Nao funciona. Experimente primeiro colocar numa célula o 008:00:00 e depois tentar formata-la no formato horas, vai repara que fica igual.
        
        Isso é que me está a ficar dificil de resolver.
        
        Obrigado pela ajuda.
        
        Responder ↓
        
        José Luís em 08/01/2016 às 12h42 disse:
        
        Pois é… Parece problema exclusivo do Excel, no LibreOffice Calc, ao digitar (ou colar) o valor em questão (008:00:00), automaticamente é armazenado o valor correto (08:00:00) na célula e ela é formatada para o formato Hora.
        
        Para reproduzir seu problema, eu tive que alterar o formato da célula para Texto e então tentar.
        
        De qualquer forma, assim que tiver acesso a alguma versão do Excel, tento novamente…
        
        Responder ↓
        
        José Luís em 08/01/2016 às 12h59 disse:
        
        Tive uma ideia (não consegui sair para almoçar sem uma possível solução para este problema): experimente uma fórmula que coloque, em uma outra célula o valor numérico corretamente formatado!
        
        em uma célula vizinha (no meu caso, B2), digite a seguinte fórmula =VALOR.TEMPO(A1); e
        
        coloque esta célula vizinha, no formato desejado, usando os passos que listei antes.
        
        Diga-me o resultado, ok?
        
        Responder ↓
        
        paulo em 08/01/2016 às 13h05 disse:
        
        Dá erro #VALOR!. Eu já descobri uma maneira de como fazer mas tenho que copiar toda a coluna no formato errado para notepad++, e a partir daí copio tudo de novo e coloco numa coluna vazia, e ao colar faço especial e corto o primeiro valor.
        
        Mas só consegui assim
        
        Responder ↓
        
        José Luís em 08/01/2016 às 14h34 disse:
        
        Consegui um Excel após o almoço e, infelizmente, cheguei ao mesmo resultado que você.
        
        Como disse antes, é uma limitação do Excel. E precisamos alterar por meio de um artifício como esse seu.
        
        Responder ↓
Lázaro em 12/03/2016 às 7h38 disse:

Gostaria de saber se tem como usando essa formula =ESQUERDA(A624;10) ou outra, se tem como fazer com que ela selecione até um carácter especifico:
ex:
035.DI0292032.jpg 45.4 kB 19/06/12 19:58:31
035.DI0301.jpg 50.5 kB 11/12/12 05:38:26
029.MU0100003.jpg 45.7 kB 19/06/12 19:58:39

O que mim interessa é só do 0 a esquerda até o pontoantes do jpg, essa formula mim permite fazer isso mais se colocar na primeira por exemplo =ESQUERDA(A1;10) vai ficar só 035.DI0292 e preciso que fique 035.DI0292032.
Resumindo tenho uma coluna com esse tipo de numeração variadas mais nesse padrão e gostaria que selecionasse até o ponto independente das quantidades de números. Desde já agradeço.

Responder ↓
- José Luís em 13/03/2016 às 16h33 disse:
  
  Seja bem-vindo, Lázaro!
  
  Acho que você pode usar a função PESQUISAR(), usando o seu resultado como parâmetro da função ESQUERDA(), que você já usa. Veja um exemplo:
  
  =ESQUERDA( A1; PESQUISAR( ".jpg"; A1 ) - 1 )
  
  Experimente e me diga o resultado, sim?
  
  Responder ↓
  - Lázaro em 15/03/2016 às 6h07 disse:
    
    Bom dia, José Luis.
    Funcionou perfeitamente, muito obrigado.
    
    Responder ↓