Neste artigo, aprenderemos diferentes maneiras de adicionar zeros (0s) na frente no Excel.
Cenário:
Adicionando Zero na frente do número no Excel. O Excel padrão não leva zeros na frente do número na célula. Você já tentou inserir alguns dados como 000123 no Excel? Você provavelmente notará rapidamente que o Excel removerá automaticamente os zeros à esquerda do número inserido. Isso pode ser realmente irritante se você quer esses zeros à esquerda nos dados e não sabe como fazer o Excel mantê-los. Felizmente, existem algumas maneiras de preencher seus números com zeros no início. Neste artigo, explicarei diferentes maneiras de adicionar ou manter os zeros à esquerda dos seus números.
Diferentes funções para adicionar zero (0) na frente
Como sabemos, a função TEXT é a função mais usada para adicionar 0 na frente, mas aqui vou explicar mais maneiras de adicionar zeros (0s) aos números.
- Função TEXT
- Função DIREITA
- Função BASE
- Adicione 0 aos valores da tabela dinâmica
Formatar como texto
Uma das soluções é converter o formato do número para texto porque se inserirmos 00xyz, o excel considera este valor como texto e mantém o seu 0 na frente. Existem diferentes maneiras de converter uma célula numérica em formato de texto.
- Usando o formato personalizado
- Usando a função TEXT
- Usando apóstrofo (') no início do valor durante a entrada
Formato Personalizado
Vamos entender esses métodos um por um. Usar um formato personalizado para alterar o formato é a maneira mais fácil de alterar os números já existentes no intervalo. Basta selecionar o intervalo e usar o atalho Ctrl + 1 ou selecionar Mais formato de número na lista suspensa da guia Página inicial que mostra Geral.
Altere o formato de Geral para Texto.
- Selecione o intervalo de células em que deseja inserir os zeros à esquerda.
- Vá para a guia Página inicial.
- Na seção Números, clique na seleção do menu suspenso Formato.
- Escolha Texto nas opções de formato.
Agora, se você tentar inserir números com zeros à esquerda, eles não desaparecerão porque foram inseridos como valores de texto em vez de números. Os usuários do Excel podem adicionar uma formatação personalizada para formatar números com zeros à esquerda.
No entanto, eles parecerão ter apenas zeros à esquerda. Os dados subjacentes não serão transformados em texto com os zeros adicionados.
Adicione um formato personalizado para mostrar os zeros à esquerda.
- Selecione o intervalo de células ao qual deseja adicionar zeros à esquerda e abra a caixa de diálogo Formatar células.
- Clique com o botão direito e escolha Formatar células.
- Use o atalho de teclado Ctrl + 1.
- Vá para a guia Número.
- Selecione Personalizado nas opções de categoria.
- Adicione um novo formato personalizado na entrada Tipo. Se você quiser que o número total de dígitos incluindo quaisquer zeros à esquerda seja 6, adicione 000000 como formato personalizado.
- Pressione o botão OK.
Função TEXT
A função TEXTO permitirá que você aplique uma formatação personalizada a qualquer número de dados que já esteja em sua planilha.
= TEXTO (valor, formato)
- Valor - este é o valor que você deseja converter em texto e aplicar a formatação.
- Formato - esta é a formatação a ser aplicada.
= TEXTO (B3, "000000")
Se você quiser adicionar zeros a um número na célula B3 para que o número total de dígitos seja 6, você pode usar a fórmula acima.
Usando apóstrofo (') no início
Você pode forçar o Excel a inserir um número como texto usando um apóstrofo à esquerda.
Isso significa que você poderá manter esses zeros à frente enquanto insere seus dados.
Este método é rápido e fácil ao inserir dados. Basta digitar um caractere 'antes de qualquer número. Isso dirá ao Excel que os dados devem ser texto e não um número.
Quando você pressiona Enter, os zeros à esquerda permanecem visíveis na planilha. O 'não estará visível na planilha, mas ainda estará lá e poderá ser visto na barra de fórmulas quando o cursor da célula ativa estiver na célula.
Função RIGHT
Outra maneira de colocar os zeros à frente com uma fórmula é usar a função RIGHT. Você pode concatenar uma sequência de zeros ao número e, em seguida, cortar os extras usando a função RIGHT.
A função RIGHT extrairá os N caracteres mais à direita de um valor de texto.
= DIREITA (Texto, [Número])
- Texto - este é o texto do qual você deseja extrair os caracteres.
- Número (Opcional) - Este é o número de caracteres a extrair do texto. Se este argumento não for inserido, apenas o primeiro caractere será extraído.
= DIREITO ("000000" & B3, 6)
A fórmula acima concatenará vários zeros ao início de um número na célula B3 e, em seguida, retornará os 6 caracteres mais à direita, resultando em alguns zeros à esquerda.
Função BASE
Você notará que este artigo descreve 9 maneiras de adicionar zeros à esquerda, mas meu vídeo do YouTube mostra apenas 8 maneiras.
Isso porque eu não sabia que você poderia usar a função BASE para adicionar zeros à esquerda até que alguém mencionasse nos comentários do vídeo.
A função BASE permite converter um número em uma representação de texto com uma determinada base.
Os números que você normalmente usa são de base 10, mas você pode usar esta função para converter um número de base 10 em uma representação de base 2 (binária).
= BASE (número, base, [comprimento mínimo])
- Número - este é o número que você deseja converter em um valor de texto em outra base.
- Base - esta é a base para a qual você deseja converter o valor.
- MinLength (Opcional) - Este é o comprimento mínimo dos caracteres do valor do texto convertido.
Use a fórmula
| = BASE (B3,10,6) |
A fórmula acima converterá um número na célula B3 em base 10 (já é um número de base 10, mas isso o manterá como base 10) e o converterá em um valor de texto com pelo menos 6 caracteres. Se o valor convertido tiver menos de 6 caracteres, ele preencherá o valor com zeros para obter um mínimo de 6 caracteres.
Definitivamente, um novo uso legal para uma função que, de outra forma, nunca utilizaria.
Adicione zero (0) apenas na frente dos números, não nos valores de texto
Aqui, consideraremos o cenário que abrange os valores de texto e números na mesma lista. Agora o Excel considera cada célula diferente em termos de formato. Agora, como deixar que o Excel saiba que se a célula tem um valor numérico, então adicione 0 na frente ou então se a célula tiver texto ou qualquer outro valor, então deixe como está.
Para isso, usaremos uma combinação de duas funções IF e função ISNUMBER. Aqui temos alguns valores para experimentar.
A fórmula genérica continua como
= IF (ISNUMBER (cell_ref), "0" & cell_ref, cell_ref)
Aqui
cell_ref: referência de célula da célula correspondente
Use a fórmula na célula
| = SE (ISNUMBER (F3), "0" & F3, F3) |
Explicação: A função ISNUMBER verifica o número da célula. A função IF verifica se a célula tem um número e adiciona zero na frente do número, caso contrário, apenas retorna o valor da célula.
Como você pode ver na imagem acima, o zero (0) é adicionado na frente do número. Vamos verificar se esta função funciona em outros valores. Copie a fórmula em outras células, selecione as células levando a primeira célula onde a fórmula já foi aplicada, use a tecla de atalho Ctrl + D
ele preencherá o valor com zeros para obter no mínimo um zero na frente dos caracteres. Definitivamente, um novo uso legal para uma função que, de outra forma, nunca utilizaria.
Coluna Calculada do Power Pivot
Existe outra opção para adicionar zeros à tabela dinâmica. Você pode adicioná-los a uma coluna calculada no Power Pivot. Desta forma, você pode usar a nova coluna na área Filtro, Linhas ou Coluna de uma tabela dinâmica.
= FORMATO (Números [Número], "000000")
No suplemento Power Pivot, você pode adicionar uma nova coluna e usar a função FORMAT para criar zeros à esquerda na coluna com uma fórmula como a acima. Uma coluna calculada calcula um valor para cada linha, portanto, não há necessidade de agrupar a função dentro de uma célula. Agora você pode usar esta nova coluna dentro da área de linhas de uma tabela dinâmica, como qualquer outra coluna de dados.
Resumo
Pode ser frustrante ver todos os seus zeros desaparecerem quando não sabemos por que isso está acontecendo ou como evitá-lo. Como você pode ver, existem várias maneiras de garantir que todos esses zeros permaneçam em seus dados. Seja qual for a sua necessidade, certamente há uma boa solução para você.
Aqui estão todas as notas de observação usando a fórmula no Excel
Notas :
- A fórmula pode ser usada tanto para textos quanto para números.
- O texto de localização e substituição deve ser classificado em uma tabela. Como usamos índices para localizar e substituir, certifique-se de que o texto 'localizar' tenha o mesmo índice do texto 'substituir'. É melhor ter as duas listas lado a lado, como fizemos no segundo exemplo.
- A fórmula genérica tem duas funções SUBSTITUTE. Você precisará de tantas funções substitutas quantas tiver substituições para fazer.
- curingas ajudam a extrair valor das substrings.
- A função suporta operadores lógicos como,, = mas eles são usados usando o sinal de aspas duplas ( " ) com argumentos. Mas se você estiver usando referência de célula para os critérios, as aspas podem ser ignoradas.
- O argumento dos critérios deve usar aspas (") para textos (como" Gillen ") ou números (como"> 50 ").
Espero que este artigo sobre Diferentes maneiras de adicionar zeros (0s) na frente no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
Função REPLACE vs SUBSTITUTE do Excel: As funções REPLACE e SUBSTITUTE são as funções mais incompreendidas. Para localizar e substituir um determinado texto, usamos a função SUBSTITUTE. Onde REPLACE é usado para substituir vários caracteres na string.
Substitua o texto do final de uma string começando na posição variável: Para substituir o texto do final da string, usamos a função REPLACE. A função REPLACE usa a posição do texto na string a ser substituída.
Como verificar se uma string contém um dos muitos textos no Excel: Para verificar se uma string contém algum dos vários textos, usamos esta fórmula. Usamos a função SUM para somar todas as correspondências e, em seguida, executamos uma lógica para verificar se a string contém alguma das várias strings.
Contar células que contêm texto específico: Uma simples função CONT.SE fará a mágica. Para contar o número de células múltiplas que contêm uma determinada string, usamos o operador curinga com a função CONT.SE.
Artigos populares :
50 atalhos do Excel para aumentar sua produtividade : Torne suas tarefas mais rápidas no Excel. Esses atalhos o ajudarão a aumentar a eficiência do trabalho no Excel.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.