Este tutorial mostra diferentes maneiras de adicionar zeros principais no Excel: como manter zeros à medida que você escreve, mostrar zeros principais nas células, remover ou esconder zeros.
Se você usar o Excel não só para calcular números, mas também manter registros como códigos zip, números de segurança ou ids de funcionários, você pode precisar manter os zeros líderes nas células. No entanto, se você tentar digitar um código zip como “00123” em uma célula, o Excel irá imediatamente truncá-lo para “123”.,
O ponto é que o Microsoft Excel trata códigos postais, números de telefone e outras entradas similares como números, aplica o formato geral ou número para eles, e automaticamente remove zeros anteriores. Felizmente, o Excel também fornece os meios para continuar liderando zeros nas células, e mais adiante neste tutorial você vai encontrar um punhado de maneiras de fazê-lo.,
- Manter os zeros à esquerda em Excel como você digite
- Mostrar zeros à esquerda, usando um formato personalizado
- Adicionar zeros à esquerda em números (função TEXTO)
- Almofada de seqüências de caracteres de texto com zeros
- Adicionar um número fixo de anterior zeros
- Remover os zeros à esquerda em Excel
- Ocultar valores zero no Excel
Como manter os zeros à esquerda em Excel como você digite
Para começar, vamos ver como você pode colocar 0 na frente de um número no Excel, por exemplo, tipo 01, em uma célula., Para isso, basta mudar o formato da célula para texto:
- seleccione a(s) célula (s) onde deseja prefixar os números com 0.
- vá para a página inicial > número de grupo, e seleccione o texto no campo Formato número.
assim que você digite um zero(s) antes do número, o Excel exibirá um pequeno triângulo verde no canto superior esquerdo da célula, indicando que algo está errado com o conteúdo da célula. Para remover esse indicador de erro, selecione a(s) célula (s), clique no sinal de aviso e, em seguida, clique em Ignorar erro.,
a seguinte imagem mostra o resultado:
outra maneira de manter os zeros iniciais no Excel é prefixar um número com um apóstrofo (‘). Por exemplo, em vez de escrever 01, escreva ’01. Neste caso, você não precisa mudar o formato da célula.
linha de fundo: esta técnica simples tem uma limitação significativa – o valor resultante é uma cadeia de texto, não número, e consequentemente não pode ser usado em cálculos e fórmulas numéricas., Se isso não é o que você quer, em seguida, alterar apenas a representação visual do valor, aplicando um formato de número personalizado, como demonstrado no próximo exemplo.
como mostrar os zeros iniciais no Excel, usando um formato personalizado de números
para mostrar os zeros iniciais, aplique um formato personalizado de números, executando estes passos:
- seleccione uma(s) célula (s) onde deseja mostrar os zeros iniciais e carregue em Ctrl+1 para abrir a janela de células de formato.
- na categoria, seleccione personalizado.
- digite um código de formato na caixa de tipo.,
na maioria dos casos, você vai precisar de um código de formato que consiste em 0 espaços, como 00. O número de zeros no código de formato corresponde ao número total de dígitos que você deseja mostrar em uma célula (você encontrará alguns exemplos abaixo).
- Carregue em OK para gravar as alterações.
Por exemplo, para adicionar zeros à esquerda para criar um número de 5 dígitos, use o seguinte formato de código: 00000
usando o Excel personalizado de formatos de números, você pode adicionar zeros à esquerda para criar números de comprimento fixo, como no exemplo acima, e números de comprimento variável., Tudo se resume a qual substituição usa no código do formato:
- 0 – mostra zeros extra
- # – não mostra zeros extra
por exemplo, se aplicar o formato 000# a alguma célula, qualquer número que escreva nessa célula terá até 3 zeros iniciais.
os seus formatos de números personalizados também podem incluir espaços, hifos, parêntesis, etc. A explicação detalhada pode ser encontrada aqui: Formato Personalizado Número Excel.
a seguinte folha de cálculo dá mais alguns exemplos de formatos personalizados que podem mostrar os principais zeros no Excel.,
E os seguintes códigos de formato podem ser usados para exibir números em formatos especiais, tais como códigos zip dos EUA, números de telefone, números de cartão de crédito e números de segurança social.,
linha Inferior: Esse método é melhor para ser usado em situações quando você trabalha com um conjunto de dados numérico e os resultados devem ser números, e não em texto. Ele muda apenas a exibição de um número, mas não o número em si: os zeros iniciais aparecem nas células, o valor real exibe na barra de fórmulas. Quando você faz referência a essas células em fórmulas, os cálculos são perfumados com os valores originais., Formatos personalizados só podem ser aplicados a dados numéricos (números e datas) e o resultado também é um número ou data.
como adicionar zeros iniciais no Excel com a função de texto
enquanto um formato de número personalizado mostra zero na frente de um número sem realmente mudar o valor subjacente, a função de texto Excel absorve números com zeros por “fisicamente” inserindo zeros líderes nas células.
para adicionar zeros iniciais com uma fórmula de texto(Valor, format_ texto), você usa os mesmos códigos de formato que em formatos de números personalizados., No entanto, o resultado da função de texto é sempre uma cadeia de texto, mesmo que se pareça muito com um número.
Por exemplo, para 0 antes de inserir um valor na célula A2, utilize esta fórmula:
=TEXT(A2, "0#")
Para criar um zero de prefixo de seqüência de caracteres de comprimento fixo, digamos, um 5-cadeia de caracteres, use esta:
=TEXT(A2, "000000")
por Favor, preste atenção que a função TEXTO exige, colocando os códigos de formato entre aspas.,ike in Excel:
| A | B | C | |
|---|---|---|---|
| 1 | Original number | Padded number | Formula |
| 2 | 1 | 01 | =TEXT(B2, “0#”) |
| 3 | 12 | 12 | =TEXT(B3, “0#”) |
| 4 | 1 | 00001 | =TEXT(B4,”00000″) |
| 5 | 12 | 00012 | =TEXT(B5,”00000″) |
For more information about Text formulas, please see How to use the TEXT function in Excel.,
linha de fundo: a função de texto Excel sempre retorna uma cadeia de texto, não número, e, portanto, você não será capaz de usar os resultados em cálculos aritméticos e outras fórmulas, a menos que você precise comparar a saída com outras cadeias de texto.
como adicionar zeros iniciais a sequências de texto
nos exemplos anteriores, aprendeu a adicionar zero antes de um número no Excel. Mas e se você precisar colocar zero (s) na frente de uma cadeia de texto como 0A102? Nesse caso, nem texto nem formato personalizado vai funcionar porque eles lidam apenas com valores numéricos.,
Se o valor a ser acolchoado com zero contém letras ou outros caracteres de texto, use uma das seguintes fórmulas, que oferecem uma solução universal aplicável tanto aos números como às cadeias de texto.
Fórmula 1. Adicionar zeros à esquerda usando a função DIREITA
A maneira mais fácil de colocar zeros à esquerda antes de seqüências de caracteres de texto no Excel é usando a função DIREITA:
em que:
- “0000” é o número máximo de zeros que você deseja adicionar. Por exemplo, para adicionar 2 zeros, você digite “00”.,
- célula é uma referência à célula que contém o valor original.
- String_length é quantos caracteres a cadeia resultante deve conter.
Por exemplo, para fazer um a zero-o prefixo 6-cadeia de caracteres com base em um valor na célula A2, utilize esta fórmula:
=RIGHT("000000"&A2, 6)
o Que a fórmula faz é adicionar 6 zeros para o valor em A2 (“000000″&A2), e, em seguida, extraia o direito de 6 caracteres., Como resultado, ele insere apenas o número certo de zeros para atingir o limite de cadeia total especificado:
no exemplo acima, o número máximo de zeros é igual ao comprimento total da cadeia (6 caracteres), e portanto todas as cadeias resultantes são de 6 caracteres (comprimento fixo). Se aplicada a uma célula em branco, a fórmula retornaria uma cadeia consistindo de 6 zeros.,
Dependendo da sua lógica de negócios, você pode fornecer diferentes números de zeros e o total de caracteres, por exemplo:
=RIGHT("00"&A2, 6)
Como resultado, você vai obter seqüências de comprimento variável que contém até 2 zeros à esquerda:
a Fórmula 2.,minério de uma seqüência de caracteres de texto no Excel é usando esta combinação de REPETIR e funções COMP:
REPT(0, número de zeros-LEN(célula))&célula
Por exemplo, para adicionar zeros à esquerda para o valor em A2 para criar um 6-cadeia de caracteres, esta fórmula vai como segue:
=REPT(0, 6-LEN(A2))&A2
Como esta fórmula funciona:
Sabendo que a função REPT repete um determinado caractere um número especificado de vezes, e LEN retorna o comprimento total da corda, a fórmula da lógica é fácil de entender:
- LEN(A2) obtém o número total de caracteres na célula A2.,
- REPT (0, 6-LEN (A2)) adiciona o número necessário de zeros. Para calcular quantos zeros devem ser adicionados, você subtrai o comprimento do texto Em A2 do número máximo de zeros.
- finalmente, você concatenar zeros com o valor A2, e obter o seguinte resultado:
linha inferior: esta fórmula pode adicionar zeros iniciais tanto a números como a textos, mas o resultado é sempre texto, não Número.,
como adicionar um número fixo de zeros anteriores
para prefixar todos os valores em uma coluna (números ou textos) com um certo número de zeros, use a função concatenada ou o operador ampersand.,
Por exemplo, para 0 antes de colocar um número na célula A2, use um dos seguintes fórmulas:
=CONCATENATE(0,A2)
ou
=0&A2
Como mostrado na captura de tela abaixo, a fórmula adiciona apenas um zero à esquerda, para todas as células em uma coluna, independentemente de quantos caracteres o valor original contém:
da mesma forma, você pode inserir 2 zeros (00), 3 zeros (000) ou como muitos zeros que você quer antes de números e cadeias de caracteres de texto.,
linha de fundo: o resultado desta fórmula é também uma cadeia de texto mesmo quando você está concatenando zeros com números.
Como remover zeros à esquerda em Excel
O método que você usa para remover os zeros à esquerda em Excel depende de como os zeros foram adicionados:
- Se anterior zeros foram adicionados com um formato de número personalizado (zeros são visíveis em uma célula, mas não na barra de fórmulas), aplicar um formato personalizado ou reverter Geral, como mostrado aqui.,
- Se os zeros foram escritos ou de outra forma introduzidos em células formatadas como texto (um pequeno triângulo verde é mostrado no canto superior esquerdo da célula), converta o texto para o número.
- Se os zeros iniciais foram adicionados usando uma fórmula (a fórmula aparece na barra de fórmulas quando a célula é selecionada), use a função de valor para removê-los.,
a imagem A seguir mostra todos os três casos para ajudar você a escolher o direito técnica:
Remover zeros à esquerda alterando o formato da célula
Se os zeros à esquerda são mostrados nas células com um formato personalizado e, em seguida, alterar o formato da célula para o padrão Geral, ou aplicar outro formato de número que não apresentar anterior zeros.,
Remover zeros à esquerda pela conversão de texto para o número
Quando o prefixo zeros aparecem em um Texto formatado da célula, a maneira mais fácil de removê-los é selecionar a célula(s), clique no ponto de exclamação e, em seguida, clique em Converter em Número:
Remover zeros à esquerda usando uma fórmula
No caso de um precedente zero(s) é adicionada com uma fórmula, use outra fórmula para removê-lo. A fórmula de remoção zero é tão simples como:
=VALUE(A2)
Onde A2 é a célula a partir da qual deseja remover zeros anteriores.,
este método também pode ser usado para se livrar de zeros tipados diretamente em células (como no exemplo anterior) ou importados para Excel a partir de alguma fonte externa. No geral, se você estiver lidando com uma cadeia de zero-prefixo que representa um número, você pode usar a função do valor para converter texto para número e remover zeros iniciais ao longo do caminho.
A imagem seguinte mostra duas fórmulas:
- a fórmula de texto Em B2 adiciona zeros ao valor em A2, e
- a fórmula de valor em C2 remove os zeros iniciais do valor em B2.,
Como ocultar zeros no Excel
Se você não deseja exibir o zero valores em sua planilha do Excel, você tem duas opções a seguintes:
- ocultar zeros em toda a planilha, desmarque a opção Mostrar um zero nas células que tem o valor zero opção., Para isso, clique em Arquivo > Opções > Avançado e role para baixo para Exibir opções para esta secção de folha de cálculo:
- Para ocultar valores zero em certas células, aplique o seguinte formato de número personalizado para as células: #;#;;@
Para isso, selecione as células onde você deseja ocultar zeros, clique em Ctrl+1 para abrir o diálogo Formatar Células, selecione Personalizado sob a Categoria, o tipo e o formato acima o código na caixa Tipo.,
A imagem abaixo mostra que a célula B2 contém um valor zero, mas não é mostrada na célula:
É assim que pode adicionar, remover e esconder zeros no Excel. Para entender melhor as técnicas descritas neste tutorial, você é bem-vindo para baixar a amostra Excel principal zeros Workbook. Agradeço-lhe por ler e espero vê-lo em nosso blog na próxima semana.,
- Como formatar células no Excel
- Personalizado Excel formato de número
- Como converter a data para texto usando o Excel função de TEXTO e não-fórmula formas
- Como trocar colunas no Excel por drag-n-drop e outras formas de se mover colunas
