Este tutorial cobre os diferentes métodos para adicionar ou manter zeros à esquerda, bem como remover zeros à esquerda no Excel.
Sempre que você insere ou importa números com um ou mais zeros à esquerda, como 000652, o Excel remove automaticamente esses zeros e apenas o próprio número é mostrado nas células (652). Isso ocorre porque os zeros à esquerda não são necessários para os cálculos e não contam.
No entanto, há momentos em que esses zeros iniciais são necessários, como quando você insere números de identificação, números de telefone, números de cartão de crédito, códigos de produto ou códigos postais, etc. Felizmente, o Excel nos oferece várias maneiras de adicionar ou manter zeros à esquerda nas células. Neste artigo, mostraremos as diferentes maneiras de adicionar ou manter os zeros à esquerda e remover os zeros à esquerda.
Adicionando Zeros à esquerda no Excel
Essencialmente, existem 2 métodos que você pode usar para adicionar zeros à esquerda: um, formate seu número como ‘Texto’; dois, use formatação personalizada para adicionar zeros à esquerda. O método que você deseja usar pode depender do que você deseja fazer com o número.
Você pode querer adicionar zero à esquerda ao inserir números de identificação exclusivos, números de contas, números de previdência social ou códigos postais, etc. Mas, você não vai usar esses números para cálculos ou funções, então é melhor convertê-los números para texto. Você nunca somaria ou faria a média de números de telefone ou contas.
Existem várias maneiras de adicionar ou preencher zeros antes dos números, formatando-os como texto:
- Alterar o formato da célula para Texto
- Adicionando um apóstrofo (‘)
- Usando a função TEXT
- Usando a função REPT / LEN
- Use a função CONCATENATE / operador E comercial (&)
- Usando a função RIGHT
Alterar o formato da célula para texto
Esta é uma das maneiras mais simples de adicionar zeros à esquerda aos seus números. Se você vai apenas inserir números e deseja manter os zeros à medida que digita, este é o método certo para você. Alterando o formato da célula de Geral ou Número para Texto, você pode forçar o Excel a tratar seus números como valores de texto e qualquer coisa que você digitar na célula permanecerá exatamente igual. Veja como você faz:
Selecione a (s) célula (s) nas quais deseja adicionar zeros à esquerda. Vá para a guia ‘Página inicial’, clique na caixa suspensa ‘Formato’ no grupo Números e selecione ‘Texto’ nas opções de formato.
Agora, quando você digitar seus números, o Excel não excluirá nenhum zero à esquerda dele.
Você pode ver um pequeno triângulo verde (indicador de erro) no canto superior esquerdo da célula e, ao selecionar essa célula, ele mostrará um sinal de aviso indicando que você armazenou o número como texto.
Para remover a mensagem de erro, selecione a (s) célula (s), clique no sinal de aviso e selecione ‘Ignorar Erro’ na lista.
Você também pode digitar números de telefone com espaço ou hífen entre os números. O Excel tratará automaticamente esses números como texto.
Usando Liderança Apóstrofo (‘)
Outra maneira de adicionar zeros à esquerda no Excel é adicionar um apóstrofo (‘) no início do número. Isso forçará o Excel a inserir um número como texto.
Basta digitar um apóstrofo antes de qualquer número e clicar em "Enter". O Excel deixará os zeros à esquerda intactos, mas o (‘) não ficará visível na planilha, a menos que você selecione a célula.
Usando a função de texto
O método acima adiciona zeros aos números conforme você os digita, mas se você já tem uma lista de números e deseja preencher zeros à esquerda antes deles, a função TEXT é o método certo para você. A função TEXT permite que você converta números em strings de texto enquanto aplica a formatação personalizada.
A sintaxe da função TEXT:
= TEXT (valor, formato_texto)Onde,
- valor - É o valor numérico que você precisa converter em texto e aplicar a formatação.
- format_text - é o formato que você deseja aplicar.
Com a função TEXTO, você pode especificar quantos dígitos seu número deve ter. Por exemplo, se você deseja que seus números tenham 8 dígitos, digite 8 zeros no segundo argumento da função: “00000000”. Se você tiver um número de 6 dígitos em uma célula, a função adicionará manualmente 2 zeros à esquerda e se você tiver números de 2 dígitos como 56, o resto será zeros (00000056).
Por exemplo, para adicionar zeros à esquerda e tornar os números de 6 dígitos, use esta fórmula:
= TEXTO (A2, "000000")Como temos 6 zeros no segundo argumento da fórmula, a função converte a sequência de números em uma sequência de texto e adiciona 5 zeros à esquerda para fazer a sequência de 6 dígitos.
Observação: Lembre-se de colocar os códigos de formato entre aspas duplas na função.
Agora você pode aplicar a mesma fórmula ao resto das células arrastando a alça de preenchimento. Como você pode ver, a função converte os números em textos e adiciona zeros à esquerda aos números para que o número total de dígitos seja 6.
A função TEXT sempre retornará o valor como uma string de texto, não um número, então você não poderá usá-los em cálculos aritméticos, mas você ainda pode usá-los em fórmulas de pesquisa como VLOOKUP ou INDEX / MATCH para buscar os detalhes de um produto usando IDs de produto.
Usando a função CONCATENATE / Operador E comercial (&)
Se você deseja adicionar um número fixo de zeros à esquerda antes de todos os números em uma coluna, você pode usar a função CONCATENAR ou o operador E comercial (&).
Sintaxe da função CONCATENATE:
= CONCATENAR (texto1, [texto2], ...)Onde,
texto 1 - O número de zeros a serem inseridos antes do número.
texto2 - O número original ou referência de célula
Sintaxe do operador E comercial:
= Valor_1 e Valor_2 Onde,
Value_1 são os zeros à esquerda a serem inseridos antes do número e Value_2 é o número.
Por exemplo, para adicionar apenas dois zeros antes de um número, use uma das seguintes fórmulas:
= CONCATENAR ("00", A2)O primeiro argumento é dois zeros (“00”) porque queremos preencher dois zeros antes do número em A2 (que é o segundo argumento).
Ou,
= "00" e A2Aqui, o primeiro argumento é 2 zeros, seguido pelo operador ‘&’, e o segundo argumento é o número.
Como você pode ver, a fórmula adiciona apenas dois zeros à esquerda a todos os números em uma coluna, independentemente de quantos dígitos o número contenha.
Ambas as fórmulas unem um certo número de zeros antes dos números originais e os armazena como strings de texto.
Usando a função REPT / LEN
Se você deseja adicionar zeros à esquerda a dados numéricos ou alfanuméricos e converter a string em texto, use a função REPT. A função REPT é usada para repetir um caractere (s) um número específico de vezes. Esta função também pode ser usada para inserir números fixos de zeros à esquerda antes do número.
= REPT (texto, numero_vezes)Onde ‘texto’ é o caractere que queremos repetir (em nosso caso ‘0’) e o argumento ‘numero_times’ é o número de vezes que queremos repetir esse caractere.
Por exemplo, para gerar cinco zeros antes dos números, a fórmula seria assim:
= REPT (0,5) e A2O que a fórmula faz é repetir 5 zeros e unir a string numérica em A2 e retornar o resultado. Em seguida, a fórmula é aplicada à célula B2: B6 usando a alça de preenchimento.
A fórmula acima adiciona um número fixo de zeros antes do número, mas o comprimento total do número varia dependendo do número.
Se você deseja adicionar zeros à esquerda onde for necessário para criar strings de caracteres específicos (comprimento fixo), você pode usar as funções REPT e LEN juntas.
Sintaxe:
= REPT (texto, número_vezes-LEN (texto)) e célulaPor exemplo, para adicionar zeros prefixados ao valor em A2 e fazer uma string longa de 5 caracteres, tente esta fórmula:
= REPT (0,5-LEN (A2)) e A2Aqui, 'LEN (A2)' obtém o comprimento total da string / números na célula A2. ‘5’ é o comprimento máximo de string / números que a célula deve ter. E a parte ‘REPT (0,5-LEN (A2))’ adiciona o número de zeros subtraindo o comprimento da string em A2 do número máximo de zeros (5). Então, um número de 0's é unido antes do valor de A2 para fazer uma string de comprimento fixo.
Usando a função RIGHT
Outra maneira de preencher zeros à esquerda antes de uma string no Excel é usar a função RIGHT.
A função RIGHT pode adicionar um número de zeros ao início de um número e extrair os N caracteres mais à direita do valor.
Sintaxe:
= DIREITO (texto, núm_caracteres)- texto é a célula ou valor do qual você deseja extrair caracteres.
- num_chars é o número de caracteres a extrair do texto. Se este argumento não for fornecido, apenas o primeiro caractere será extraído.
Para este método, concatenamos o número máximo de zeros com a referência de célula que contém a string no argumento 'texto'.
Para criar um número de 6 dígitos com base na string numérica em A com zeros à esquerda, tente esta fórmula:
= DIREITO ("0000000" e A2,6)O primeiro argumento (texto) da fórmula adiciona 7 zeros ao valor em A2 (“0000000” e A2) e retorna os 7 caracteres mais à direita, o que resulta em alguns zeros à esquerda.
Adicionando zeros à esquerda usando formatação de número personalizada
Se você usar qualquer um dos métodos acima para colocar zeros à esquerda antes dos números, sempre obterá uma string de texto, não um número. E eles não serão muito úteis em cálculos ou em fórmulas numéricas.
A melhor maneira de adicionar zeros à esquerda no Excel é aplicar uma formatação de número personalizada. Se você adicionar zeros à esquerda, adicionando um formato de número personalizado à célula, isso não altera o valor da célula, mas apenas a forma como é exibida. O valor ainda permanecerá como um número, não como um texto.
Para alterar a formatação do número das células, siga estas etapas:
Selecione a célula ou intervalo de células onde deseja mostrar os zeros à esquerda. Em seguida, clique com o botão direito em qualquer lugar dentro do intervalo selecionado e selecione a opção ‘Formatar células’ no menu de contexto. Ou pressione as teclas de atalho Ctrl + 1.
Na janela Formatar células, vá para a guia ‘Número’ e selecione ‘Personalizar’ nas opções de categoria.
Insira o número de zeros na caixa ‘Tipo:’ para especificar o número total de dígitos que você deseja mostrar em uma célula. Por exemplo, se você deseja que o número tenha 6 dígitos, digite '000000' como código de formato personalizado. Em seguida, clique em ‘OK’ para aplicar.
Isso mostrará zeros à esquerda antes dos números e se o número tiver menos de 6 dígitos, ele preencherá zero antes dele.
Os números parecerão ter apenas zeros à esquerda, enquanto o valor subjacente permanecerá inalterado. Se você selecionar uma célula com formatação personalizada, ela mostrará o número original na barra de fórmulas
Existem muitos marcadores de posição digital que você pode usar em seu formato de número personalizado. Mas existem apenas dois marcadores de posição primários que você pode usar para adicionar zeros à esquerda nos números.
- 0 – É o marcador de posição do dígito que exibe zeros extras. Ele exibe dígitos forçados de 0 a 9, independentemente de o dígito ser ou não relevante para o valor. Por exemplo, se você digitar 2,5 com o código de formato 000,00, será exibido 002,50.
- # – É o marcador de posição de dígito que exibe dígitos opcionais e não inclui zeros extras. Por exemplo, se você digitar 123 com o código de formatação 000 #, será exibido 0123.
Além disso, qualquer sinal de pontuação ou outro caractere incluído no código de formato será exibido como está. Você pode usar caracteres como hífen (-), vírgula (,), barra (/), etc.
Por exemplo, você também pode formatar números como números de telefone usando um formato personalizado.
A caixa de diálogo Formatar código na caixa de diálogo Formatar células:
O resultado:
Vamos aplicar este código de formatação no seguinte exemplo:
##0000
Como você pode ver, '0' adiciona zeros extras, enquanto '#' não adiciona zeros insignificantes:
Você também pode usar códigos de formato predefinidos na seção ‘Formatos especiais’ da caixa de diálogo Formatar células para códigos postais, números de telefone e números de previdência social.
A tabela a seguir mostra números com zeros à esquerda onde diferentes códigos de formato ‘especial’ são aplicados a diferentes colunas:
Removendo Zeros à esquerda no Excel
Agora que você aprendeu como adicionar zeros à esquerda no Excel, vamos ver como remover os zeros à esquerda do número de strings. Às vezes, quando você importa dados de uma fonte externa, os números podem acabar tendo zeros de prefixo e sendo formatados como texto. Nesses casos, você precisa remover os zeros à esquerda e convertê-los novamente em números, para que possa usá-los nas fórmulas.
Existem várias maneiras de remover zeros à esquerda no Excel e vamos vê-los um por um.
Remova os zeros à esquerda alterando a formatação da célula
Se zeros à esquerda foram adicionados pela formatação de número personalizada, você pode removê-los facilmente alterando o formato das células. Você pode saber se suas células estão formatadas de forma personalizada observando a barra de endereço (os zeros estarão visíveis na célula, não na barra de endereço).
Para remover os zeros prefixados, selecione as células com os zeros à esquerda, clique na caixa ‘Formato do número’ e selecione a opção de formatação ‘Geral’ ou ‘Número’.
Agora, os zeros à esquerda se foram:
Exclua os zeros à esquerda convertendo o texto em números
Se os zeros à esquerda foram adicionados alterando o formato da célula ou adicionando apóstrofos antes dos números ou adicionados automaticamente ao importar dados, a maneira mais fácil de convertê-los em números é usando a opção de verificação de erros. Veja como você faz:
Você pode usar este método se seus números estiverem alinhados à esquerda e suas células tiverem um pequeno triângulo verde (um indicador de erro) no canto superior esquerdo das células. Isso significa que os números são formatados como texto.
Selecione essas células e você verá um aviso amarelo na parte superior direita da seleção. Em seguida, clique na opção ‘Converter em número’ no menu suspenso.
Seus zeros serão removidos e os números serão convertidos de volta para o formato de número (alinhado à direita).
Removendo Zeros à esquerda por Multiplicar / dividir por 1
Outra maneira fácil e melhor de remover o entrelinha é multiplicar ou dividir os números por 1. Dividir ou multiplicar o valor não altera o valor, simplesmente converte o valor de volta em um número e remove os zeros iniciais.
Para fazer isso, digite a fórmula no exemplo abaixo em uma célula e pressione ENTER. Os zeros à esquerda serão removidos e a string será convertida novamente em um número.
Em seguida, aplique esta fórmula a outras células usando a alça de preenchimento.
Você pode obter os mesmos resultados usando o comando ‘Colar especial’. Veja como:
Digite o valor numérico ‘1’ em uma célula (digamos em B2) e copie esse valor.
Em seguida, selecione as células nas quais deseja remover os zeros à esquerda. Em seguida, clique com o botão direito na seleção e selecione a opção ‘Colar especial’.
Na caixa de diálogo Colar especial, em Operação, escolha a opção ‘Multiplicar’ ou ‘Dividir’ e clique em ‘OK’.
É isso, seus zeros à esquerda serão removidos, deixando as strings como números.
Remova os zeros à esquerda usando fórmulas
Outra maneira fácil de excluir zeros prefixados é usando a função VALUE. Este método pode ser útil se os zeros à esquerda forem adicionados usando outra fórmula ou apóstrofo ou por formatação personalizada.
= VALOR (A1)O argumento da fórmula pode ser um valor ou a referência de célula que possui o valor. A fórmula remove os zeros à esquerda e converte o valor de texto em número. Em seguida, aplique a fórmula ao restante das células.
Às vezes, você pode querer remover os zeros à esquerda, mas deseja manter os números no formato de texto. Nesses casos, você deve usar as funções TEXT () e VALUE () juntas, assim:
= TEXTO (VALOR (A1), "#")A função VALUE converte o valor de A1 em número. Mas o segundo argumento, ‘#’ converte o valor de volta para o formato de texto sem zeros extras. Como resultado, você obteria os números sem zeros à esquerda, mas ainda no formato de texto (alinhado à esquerda).
Remova os zeros à esquerda usando o recurso de texto em colunas do Excel
Outra maneira de remover zeros à esquerda é usar o recurso Texto para Colunas do Excel.
Selecione o intervalo de células que possuem os números com zeros à esquerda.
Em seguida, vá para a guia ‘Dados’ e clique no botão ‘Texto para colunas’ no grupo Ferramentas de dados.
O assistente ‘Converter texto em colunas’ aparecerá. Na etapa 1 de 3, selecione ‘Delimitado’ e clique em ‘Avançar’.
Na etapa 2 de 3, desmarque todos os delimitadores e clique em ‘Avançar’.
Na etapa final, deixe a opção Formato de dados da coluna como ‘Geral’ e escolha o destino (a primeira célula do intervalo) onde deseja seus números sem zeros à esquerda. Em seguida, clique em ‘Concluir’
E você obterá os números com a liderança removidos em uma coluna separada, conforme mostrado abaixo.
Isso é tudo.