Questão Por que o Excel trata cadeias numéricas longas como notação científica mesmo depois de alterar o formato das células para texto


Estou tentando processar alguns dados no Excel. Os dados incluem números de contas numéricas e outras cadeias numéricas longas (como MEIDs de telefone celular). Eu não estou fazendo operações matemáticas nessas seqüências de caracteres, então eu quero que o Excel tratá-los como texto simples.

Aqui está o que está me deixando louco (isso é o Excel 2010):

  1. Pegue um número longo como 1240800388917 e cole-o em uma célula em uma nova planilha.
  2. O formato de célula padrão do Excel é geral, então a string é apresentada em notação científica como 1.2408E + 12
  3. Clique com o botão direito na célula, selecione Formatar células, defina o formato como Texto

A célula ainda é exibida em notação científica, mesmo que o formato tenha sido definido como texto.

Agora, se eu seguir as etapas em uma ordem diferente:

  1. Formate uma célula vazia como texto. Clique com o botão direito na célula, selecione Formatar células, defina o formato como Texto
  2. Pegue um número longo como 1240800388917 e cole-o na célula formatada em texto

Agora, a célula é exibida como uma string e não em notação científica.

Os resultados que permanecem em notação científica, mesmo que a célula esteja formatada como texto, parecem estar quebrados para mim. Eu vi sugestões de soluções como: use a importação de CSV e defina o formato como texto, adicione um caractere de espaço ao início de cada sequência numérica e outros.

Existe um bom trabalho simples para manter essas sequências formatadas como texto?

Por que o Excel faz isso?

Perguntas relacionadas ao SU que encontrei: Como você pode fazer o Excel 2007 parar de formatar grandes números como notação científica? e Este comportamento do Excel com um grande número hexadecimal é esperado?


88


origem


O longo número 1240800388917 é copiado de outra célula? Ou apenas texto simples, como no Bloco de Notas? - wilson


Respostas:


Ah, memórias de dados voltando de quando eu fiz um grande número de cruzamentos no Excel (nunca mais) .. Quando você digitou strings numéricas longas no Excel, por exemplo, 12345678901234567890 (20 dígitos), o Excel geralmente converter para você, significando que o número de 20 dígitos que você acabou de digitar foi reduzido a apenas cerca de quinze números significativos.

Observe também: a conversão é um truncamento, ao contrário do arredondamento. (12345678901234567890 é 1.23456789012346E + 19, mas o excel mostrará 1.23456789012345E + 19)

Isso ocorre no ponto de entrada, portanto, uma vez dentro, qualquer detalhe adicional é perdido, e mesmo se você disser ao Excel que realmente quis dizer que era texto, e não um número, você está meio sem sorte e, por isso, sua primeira sequência não funciona.


20



Sim, vejo esse comportamento quando minha string numérica possui mais de 15 dígitos significativos. 12345678901234567890 se torna 1.23456789012345E + 19 como você diz. Quando eu clico na célula, a caixa de edição mostra a string truncada e preenchida com zeros como 12345678901234500000. O que é estranho é que ela faz a mesma coisa para números que não excedem o limite de 15 dígitos do Excel. Se eu colar em 12345678901234, ele será exibido como 1.23457E + 13, mas quando clico nele, a caixa de edição ainda mostra a string original. Agora, o que é estranho é se eu formatar esta célula para texto, ela ainda é mostrada em notação científica. Irritante. - Michael Levy
Aquele é um pouco de um truque de exibição na verdade - definir o formato de geral para texto não o mostrará imediatamente como a string original, mas se você entrar e editar os dados (como em, basta clicar na zona de edição e clicar fora ) vai atualizar para ser exatamente o que você coloca. - tanantish
Se eu clicar na célula, posso ver que o Excel tem o número inteiro, mas, independentemente de eu fazer todo o texto das células, ele ainda quer usar a notação científica. Tão frustrante! - The Muffin Man
Quick Hack: Defina a formatação nas colunas apropriadas, copie e cole no bloco de notas e, em seguida, no Excel. - Brian
Isto deveria NÃO SER A RESPOSTA ACEITADA: veja o próximo abaixo. - javadba


Isso funcionou para mim no Excel 2010. Basta selecionar a coluna, clique com o botão direito, Formatar células, Personalizar e escolha a opção que diz 0 (segunda opção abaixo Geral).


128



Isso é muito melhor do que a resposta aceita que diz "você está meio sem sorte"! Eu tinha um arquivo CSV que tinha esse problema e a solução de formatação personalizada funcionava perfeitamente. - davidair
deve ser aceite resposta, alguém deve / poderia mudar? - qwertzman
Isso ainda parece truncar no meu teste. Usando o Excel 2013 eu inseri o seguinte em uma célula 12345678901234567890. Quando eu pressionar enter, isso é exibido como 1.23457E + 19 e o valor subjacente é alterado para 12345678901234500000, portanto, há perda de dados. Isso não responde à pergunta original. - Michael Levy
O problema é que seus números não são formatados como texto, mas como números quando você tenta isso. E isso abre um monte de outras advertências. - Joris Meys
Aqui estou um ano depois revisitando a mesma resposta e desejando poder revê-la novamente. - billynoah


Um único apóstrofo 'antes de um número forçará o Excel a tratar um número como texto (incluindo um alinhamento à esquerda padrão). E se você tiver erros sinalizados, ele será exibido como um número armazenado como erro de texto na célula.


12



Isso não é sempre o caso. Às vezes, o Excel mexeu meus dados mesmo com um apóstrofo principal. - Cerin
Cerin, um ponto justo, nunca subestima a capacidade do Excel (ou da Microsoft) de fazer algo completamente inexplicável ou repetitivo. No entanto, o truque de apóstrofo geralmente funciona como anunciado. - dav
você pode fazer isso em massa? Eu tenho dados com 60000 células que eu quero converter em texto. - vaibhavcool20


Descobri que no Office 2010, se você formatar a coluna primeiro e depois colar os dados, ela mostrará os números grandes corretamente.


10



Isso é provavelmente porque é armazenado como uma string. O que acontece se você fizer outra célula ser, digamos, o conteúdo da célula multiplicado por 2? - Peter Mortensen
Isso funciona no Excel 2016 / Office 365. Selecionei todas as células, formatadas em Text e colei meus dados copiados e ele não apareceu com a notação científica irritante. Obrigado! - frezq


Tente isso ... isso funciona para mim, Excel 2013.

=""&a1

onde a1 contém o valor numérico. Depois, copie a coluna e cole na coluna original.


10



Isso funciona no Excel 2010 também. - Paul G
Como um nó lateral: ="1240800388917" faz o mesmo - nixda


Você pode formatar células como personalizadas> #

Isso pode ser aplicado depois de colar os dados na coluna, mas você deve fazer isso antes de salvar a planilha.

Eu trabalho muito com códigos de barras (UPCs) e o excel irá formatá-los como notação científica por padrão. Me deixa louca! Por que isso não é uma opção que eu nunca saberei.


4



Isso não transforma os números em texto, o que pode causar problemas mais tarde. Mas, bem... - Joris Meys


Excel 2010: Isso funciona uma coluna de cada vez e não para uma linha. Selecione a coluna ou subconjunto de uma coluna, na guia Dados, selecione "Texto para Colunas" e pule para a direita para "Concluir". Não há mais notação científica.

Eu concordo com os muitos usuários do Excel, é criminoso que não há opção para impedir a notação científica!


3



No meu teste no Excel 2007, eu tive que clicar Next > duas vezes, selecione “Texto” e clique em Finish. Você pode aplicar isso às linhas, transpondo, convertendo e transpondo de volta. - Scott


Estou ciente da idade da questão, mas esta é a que eu desembarquei após a pesquisa do Google e que não respondeu a minha pergunta (porque a formatação como texto não funciona e porque o número é reduzido para 15 dígitos após o formato para texto).

Resposta curta é: você não pode trabalhar no seu número da maneira que você gosta DEPOIS que você digitou o valor numérico e tocou Enter. No momento em que você fizer isso, o número será truncado para 15 dígitos e apresentado como exponencial.

Isso não é um bug, é um recurso.

Qualquer coisa que você fizer depois disso estará usando o valor truncado, portanto, nenhum truque de formatação ajudará. No entanto, você pode usar o Text to columns opção para converter notação exponencial em texto (Clique no cabeçalho da coluna, clique em Text to data, então Delimited, Next, desmarque todas as caixas delimitadoras, selecione Text dentro Column data format e depois Finish) ter os valores convertidos em texto e exibidos imediatamente como um número de 15 dígitos. Mas, serão apenas 15 dígitos, então se você tiver um número maior, o descanso será perdido.

Para ter o número na planilha exatamente a maneira que você digitou em você tem que armazená-lo como texto. Então você tem que preceder com apóstrofo ou formatar células como texto antes digitando / copiando valores para ele. Se "às vezes não funcionar", então, desculpe, você faz algo errado ou tem algum tipo de correção automática (se você usa apóstrofo e número grande> 15 dígitos, o Excel o trata como um valor errado e sinaliza a célula com aviso mensagem, então esta observação não é pessoal nem crítica).

Outra maneira de fazer isso em números em massa é importar valores do arquivo de texto. Eu ouso dizer que é o único caminho para a maioria das situações. E certifique-se de importar o arquivo, não apenas abri-lo, pois o Excel trata csv digite como se fosse o formato nativo e apara números grandes a 15 dígitos, como é óbvio.

Agora, para a última edição. Se você inserir um grande valor numérico na célula do Excel formatado como texto, ele ainda será exibido em notação científica, desde que a célula não seja larga o suficiente. É irritante, mas efeito colateral de alguns algoritmos internos do Excel. Apenas faça o celular mais amplo e você verá o valor total toda vez.


2





No Excel 2013, a mesma coisa aconteceu comigo e eu fui para Formatar Células -> Número, em seguida, certifiquei-me de que a casa decimal estivesse configurada para "0".


1



semelhante a resposta anônima acima, mas selecionando "Número" de alguma forma faz mais sentido do que selecionar "personalizado" .. além disso, é maior na lista. de qualquer forma, faz a mesma coisa, mas eu gosto disso melhor - billynoah


Selecione todas as células - ou, para facilitar as coisas, selecione a planilha inteira e clique em Formatar celulas, então Texto.

Automaticamente, toda a sua planilha usará texto. Você não precisa fazer isso célula por célula.


0



Infelizmente, isso não funciona. O texto será apenas em notação científica, esse é o problema todo ... - Joris Meys


  1. Copie / cole o campo do problema na nova folha na coluna A

  2. Copie / cole a coluna A no arquivo de texto

  3. Formate a coluna B para texto e cole o arquivo de texto na coluna B

  4. Na Coluna C:

    =IF(ISNUMBER(SEARCH("+",B1)),A1,"")
    
  5. Converter a coluna C em um formato numérico sem decimais

  6. Copie / cole a coluna C no arquivo de texto

  7. Formate a Coluna D em texto e cole o arquivo de texto na Coluna D

  8. Na Coluna E:

    =IF(IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2))=0,"",IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2)))
    
  9. Copie / cole a coluna E no bloco de notas

  10. Excluir dados no campo de problema e formatar coluna para texto

  11. Copie / cole dados do arquivo de texto para o campo de problema


0