Existem situações em que você precisa adicionar zeros à esquerda a um conjunto de dados no Excel. Esse pode ser o caso se você mantiver registros no Excel, como IDs de funcionários ou IDs de transações.
Por exemplo, você pode desejar obter uma aparência consistente em seu conjunto de dados, conforme mostrado abaixo:
Neste tutorial, você aprenderá várias maneiras de adicionar zeros à esquerda no Excel:
- Convertendo o formato em texto
- Usando formatação de número personalizada
- Usando a função de texto
- Usando funções REPT / LEN
- Usando VBA
Cada um desses métodos tem alguns méritos e desvantagens (abordados em cada seção).
Vamos ver como cada um deles funciona.
Adicionar zeros à esquerda convertendo o formato em texto
Quando usar: Quando você tem um pequeno conjunto de dados numéricos e planeja fazer essa edição manualmente.
Suponha que você tenha IDs de funcionários do departamento de marketing conforme mostrado abaixo e deseja fazer com que esses IDs pareçam consistentes adicionando zeros à esquerda.
Portanto, você tenta alterar o id inserindo zeros à esquerda (00001 em vez de 1).
Mas, para sua surpresa, o Excel o converte de volta para 1.
Isso acontece porque o Excel entende que 00001 e 1 são os mesmos números e devem seguir as mesmas regras de exibição.
Por mais frustrante que possa ser para você, o Excel tem suas razões.
Portanto, para realizar o trabalho sem dobrar as regras do Excel, você terá que aproveitar o fato de que essa regra não se aplica à formatação de texto.
Então, aqui está o que você precisa fazer:
- Selecione as células nas quais deseja adicionar zeros à esquerda.
- Vá para Página inicial → Grupo de números e selecione Texto no menu suspenso.
É isso!
Agora, quando você insere zeros à esquerda manualmente, o Excel obedece prontamente.
Cuidado: Ao converter o formato para Texto, algumas funções do Excel não funcionarão corretamente. Por exemplo, a função SOMA / CONTAGEM ignora a célula, pois está no formato de texto.
Adicionar zeros à esquerda usando formatação de número personalizada
Quando usar: Quando você tem um conjunto de dados numérico e deseja que o resultado seja numérico (não texto).
Quando você exibe um número em um formato específico, isso não altera o valor subjacente do número. Por exemplo, posso exibir o número 1000 como 1000 ou 1.000 ou 1000,00 ou 001000 ou 26-09-1902 (datas pares são números no back-end do Excel).
Em todas as diferentes maneiras de exibir o número, o valor do número nunca muda. É apenas a forma como ela é exibida que é alterada.
Para adicionar zeros à esquerda, podemos formatá-lo para mostrar dessa forma, enquanto o valor subjacente permaneceria inalterado.
Aqui estão as etapas para usar essa técnica para adicionar zeros à esquerda no Excel:
- Selecione as células nas quais deseja adicionar zeros à esquerda.
- Vá para Home → Grupo de números e clique no inicializador de diálogo (uma pequena seta inclinada no canto inferior direito). Isso abrirá a caixa de diálogo Formatar células. Como alternativa, você também pode usar o atalho de teclado: Control + 1.
- Na caixa de diálogo Formatar células, na guia Número, selecione Personalizado na lista Categoria.
- No campo Tipo, insira 00000
- Clique OK.
Isso sempre exibirá todos os números como cinco dígitos, onde os zeros iniciais são adicionados automaticamente se o número tiver menos de 5 dígitos. Portanto, 10 se tornaria 00010 e 100 se tornaria 00100.
Nesse caso, usamos seis zeros, mas se seus dados tiverem números com mais dígitos, você precisará usar o formato de acordo.
Nota: Esta técnica funcionaria apenas para um conjunto de dados numéricos. No caso de você ter IDs de funcionário como A1, A2, A3 e assim por diante, eles são de texto e não mudariam quando você aplicasse o formato personalizado mostrado acima.
Adicionar zeros à esquerda usando a função TEXT
Quando usar: Quando você deseja que o resultado seja um texto.
A função TEXT permite que você altere o valor para o formato desejado.
Por exemplo, se quiser que 1 seja exibido como 001, você pode usar a função TEXTO para isso.
No entanto, lembre-se de que a função TEXTO mudaria o formato e o tornaria TEXTO. Isso significa que quando você cria 1 como 001, o Excel trata o novo resultado como texto com três caracteres (como abc ou xyz).
Aqui está como adicionar zeros à esquerda usando a função TEXT:
- Se você tiver os números na coluna A (digamos de A2: A100), selecione B2: B100 e insira a seguinte fórmula:
= TEXTO (A2, ”00000 ″) - Pressione Control + Enter para aplicar a fórmula a todas as células selecionadas.
Isso exibirá todos os números como cinco dígitos, onde os zeros iniciais são adicionados automaticamente se o número tiver menos de 5 dígitos.
Um benefício de converter dados em texto é que agora você pode usá-los em fórmulas de pesquisa, como VLOOKUP ou INDEX / MATCH para buscar os detalhes de um funcionário usando seu ID de funcionário.
Nota: Esta técnica funcionaria apenas para um conjunto de dados numéricos. No caso de você ter ids de funcionários como A1, A2, A3 e assim por diante, eles são de texto e não mudariam quando você aplicasse o formato personalizado mostrado acima.
Adicionar zeros à esquerda usando as funções REPT e LEN
Quando usar: Quando você tem um conjunto de dados numérico / alfanumérico e deseja que o resultado seja um texto.
A desvantagem de usar a função TEXT era que ela funcionaria apenas com dados numéricos. Mas no caso de você ter um conjunto de dados alfanumérico (digamos A1, A2, A3 e assim por diante), a função TEXT falhará.
Nesses casos, uma combinação das funções REPT e LEN resolve o problema.
Aqui está como fazer:
- Se você tiver os números na coluna A (digamos de A2: A100), selecione B2: B100 e insira a seguinte fórmula:
= REPT (0,5-LEN (A2)) e A2 - Pressione Control + Enter para aplicar a fórmula a todas as células selecionadas.
Isso tornaria todos os números / strings de 5 caracteres com zeros à esquerda sempre que necessário.
Veja como funciona esta fórmula:
- LEN (A2) fornece o comprimento da string / números na célula.
- = REPT (0,5-LEN (A2)) daria o número 0 que deve ser adicionado. Aqui, usei 5 na fórmula, pois esse era o comprimento máximo de string / números em meu conjunto de dados. Você pode alterar isso de acordo com seus dados.
- = REPT (0,5-LEN (A2)) & A2 simplesmente adicionaria o número de zeros ao valor da célula. Por exemplo, se o valor na célula for 123, isso retornará 00123.
Adicionar zeros à esquerda usando a função personalizada (VBA)
Se adicionar zeros à esquerda no Excel é algo que você precisa fazer com frequência, usar uma função personalizada é uma boa ideia.
Aqui está o código VBA que criará uma função simples para adicionar zeros à esquerda:
'Código por Sumit Bansal de http://trumpexcel.com Função AddLeadingZeroes (ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len (ref) For i = 1 To Length If i <= StrLen Then Result = Result & Mid (ref, i, 1) Else Result = "0" & Result End If Next i AddLeadingZeroes = Result End Function
Basta adicionar este código à janela de código do módulo e você poderá usá-lo como qualquer outra função de planilha.
Ou crie um add-in para ele e possa compartilhá-lo com seus colegas.
