Remover zeros à esquerda no Excel

msoffice_jigsaw.jpg

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… :sad:

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? :wink:

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

27 comentários sobre “Remover zeros à esquerda no Excel

  1. 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..

    • 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). :wink:

  2. 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!!!!!!

  3. 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á!!!

      • 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:

        1. a coluna A contém os textos onde a pesquisa ocorrerá;
        2. a coluna B exibirá o resultado; e
        3. 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!

  4. 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.

  5. 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?

    • 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

      • 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

  6. 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.

    • 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))

  7. 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!

    • 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).

  8. 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

    • 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… :wink:

  9. 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..

    • 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”.

      • 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

  10. 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.

    • 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.

  11. 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.

    • 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?

Deixe um comentário: