Impeça o Excel de converter seus dados em notação científica ao importar dados de um arquivo CSV ou TXT.
Assistir vídeo
- Você tem espaços que o TRIM não remove
- Você tem um número de peça que termina com e e um dígito
- Você tem um número de peça com mais de 15 dígitos
- Se você importar como um arquivo CSV, os números das peças mudam para notação científica
- Como mostrar extensões no Windows Explorer
- Se você importar abrindo um arquivo .txt, poderá tentar especificar que as colunas são de texto, mas
- quando você encontra / substitui o espaço não separável (caractere 160), os números das peças mudam para notação científica
- A solução é usar Dados, Obter Dados Externos, Do Texto.
- No entanto, esse comando está faltando no Office 365, tendo sido substituído por Get & Transform.
- Se você não tiver Do texto, clique com o botão direito na barra de ferramentas de acesso rápido e personalize
- No menu suspenso superior esquerdo, mude para Todos os comandos. Localizar a partir do texto (legado) e adicionar ao QAT
- Você pode abrir um arquivo CSV usando Do Texto e ele permitirá que você vá para o assistente de importação de texto
- Na etapa 2 do assistente, especifique uma vírgula e alt = "" + 0160 como personalizado. Trate delimitadores consecutivos como um só.
- Agradecimentos a Jan Karel: aqui
- Não se esqueça de votar: aqui
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2087: Prevent Scientific Notation on Import
Uau! Vamos lidar com muitas questões diferentes hoje. Então, várias pessoas enviam problemas semelhantes. Ou temos um número de peça - veja este número de peça aqui, do penúltimo dígito. É tudo numérico, mas o penúltimo dígito é uma letra: D, E, F. Esses E's vão ser um problema. Esses E's virão como notação científica ou qualquer número de peça completamente numérico com mais de 15 dígitos terá um problema.
Além disso, muitas pessoas têm me perguntado sobre - ou sobre como obter dados desse sistema baseado na web e há espaços antes e depois disso que não estão indo bem com o TRIM. Então, se você tiver algum desses três problemas, potencialmente, potencialmente irei ajudá-lo.
Tudo bem agora, a primeira coisa que vamos fazer aqui é dar uma olhada neste arquivo CSV, certo? E vou apenas clicar duas vezes para abrir; e por ser um arquivo CSV, eles não se preocupam em nos levar pelo Text Import Wizard que é horrível, certo? E então, você vê que temos alguns problemas. Primeiro, por causa do E, qualquer coisa com E entrava em notação científica. E se tentarmos consertar isso, volte para um número ou algo parecido. Perdemos. Perdemos coisas. A mesma coisa com as coisas com mais de 16 caracteres, mesmo se você alterá-los novamente para números, você terá problemas porque perdeu os últimos dígitos, certo. É simplesmente horrível.
E isso - Oh, isso é ótimo! Isso é incrível. Olhe para isso, uh sim, veio sem conversão. Oh, mas há espaços à esquerda e à direita, nossos VLOOKUPS não vão funcionar. Tudo bem agora, a primeira coisa que queremos fazer é descobrir o que esses espaços iniciais e finais são, porque se eu tentar = cortar, = cortar aquela coisa, ela não vai embora. E como posso saber que não vai embora? Porque eu posso concatenar um asterisco = “” antes e - E você vê que ainda há algo lá, certo? E quando isso acontece, ainda há algo lá. Você sabe por que TRIM não está fazendo - TRIM deve se livrar dos espaços à esquerda e à direita. Então, aqui está o que eu faço. Quanto ao = CÓDIGO da ESQUERDA disso, 1 para ver o que é, e é um caractere 160. Oh, não é isso que devemos obter.Desta vez, um antigo espaço normal ali, apenas um espaço. Aperte a barra de espaço, é um personagem 32. É um espaço real do qual o TRIM se livra. O que diabos é um 160? A 160 é um espaço ininterrupto. Este é realmente um popular em torno de sites porque se você está construindo uma página da web, você coloca espaço, espaço, espaço. Bem, o Internet Explorer e o Chrome farão desse espaço um único espaço. Mas se você colocar espaços não separáveis, 3 deles, os 3 espaços serão mantidos.Mas se você colocar espaços não separáveis, 3 deles, os 3 espaços serão mantidos.Mas se você colocar espaços não separáveis, 3 deles, os 3 espaços serão mantidos.
Tudo bem, então agora, aqui está a coisa frustrante que temos que fazer. Para se livrar desses 160 espaços, você deve ser capaz de digitar um caractere 160, o que significa que você deve ter um teclado numérico. Tudo bem agora, preste atenção enquanto eu faço isso. Vou segurar a tecla alt = "" e agora com o teclado numérico 0160, solto e pronto. Veja, simplesmente apareceu, certo? Agora, se você tiver sorte o suficiente para ter um teclado numérico, bem então, este problema aqui será Ctrl + H, em Encontre o que mantenha pressionado Alt + 0160, solte e Substitua por nada, Substitua Tudo. Feito isso, fizemos 34 substituições. Mas eu vou ser um filho da mãe, eles mudaram esses números para notação científica, tudo bem. Então, consegui colocá-los, mas ainda tenho a chance de mudar para a notação científica.
Agora, a propósito, se você não tiver um teclado numérico para digitar Alt + 0160, usar os números na parte superior não funcionará. Esqueça isso, nunca vai funcionar. Portanto, se você precisava desesperadamente digitar o caractere 160 = CHAR (160), não pressione Enter, pressione F9 para avaliar isso. Tudo bem, agora nessa célula eu tenho um único espaço, mas não é um caractere 32, é um caractere 160 e vou segurar a tecla Shift e pressionar a tecla de seta para a esquerda para selecionar isso. Ctrl + C, agora está na minha área de transferência. Agora, vamos vir aqui. Escolha essas duas colunas, Ctrl + H, Descubra o que: vou colar lá Ctrl + V. Substitua por: Nada. Substitua tudo, clique em Ok, clique em Fechar. E, novamente, estou voando baixo porque eles converteram tudo isso em notação científica.
Tudo bem agora, o que normalmente digo às pessoas para fazerem, o que normalmente diria às pessoas para fazer é voltar ao Windows Explorer e convertê-lo de um arquivo CSV para um arquivo .txt. Agora aqui, eu não consigo ver isso. Se você não - se não estiver vendo as extensões, pressione a tecla alt = "" e, em seguida, Ferramentas e, em seguida, Opções de pasta e bem aqui em Exibir, onde diz Ocultar extensões para tipos de arquivo conhecidos, desmarque. Essa é a pior configuração de todos os tempos. Eu desligo isso o tempo todo. Desejo ver a extensão dessa forma, posso clicar com o botão direito do mouse, renomear e alterá-la para .txt. Certo, qual é a vantagem de acessar .txt? Oh, ei, é incrível. Quando eu faço um .txt, porque se eu for em Arquivo e Abrir, vamos navegar até essa pasta. E eu abro a versão .txt, clico em OK. Tudo bem agora, ei,Eu consigo passar e dizer em cada etapa que tipo é e então posso dizer - Ah, vamos quebrar pela Vírgula. Sim, lindo. E em seguida, e bem aqui vou dizer, não mexa com isso. O texto é a maneira de dizer não mexa com isso. Mesma coisa aqui, não mexa com isso. Estes, não mexa com eles, Texto, Texto, Texto. E normalmente não gostamos de usar Texto, mas aqui onde eles estão mudando meus números, usar Texto permitirá que eles entrem e não serão notação científica. Uau! Fantástico. E esta é a maneira que eu sempre sugeri resolver esse problema, mas então eu vi este ótimo artigo de um amigo meu, Jan Karel, JKP Application Development Services, que me mostrou uma maneira nova e brilhante. Uma maneira nova e brilhante. Então, deixe-me mostrar isso. EU'Colocarei o link para este artigo no comentário do YouTube. Certifique-se de verificar o artigo.
Tudo bem, vamos voltar aqui e o bonito é que não precisamos renomear de texto - de CSV para texto, porque lidará com CSV, o que é muito bom porque se estivermos obtendo este arquivo todos os dias, queremos ser capazes de lidar com CSV. Agora, aqui está uma coisa maluca. Se você estiver no Excel 2013 ou anterior, queremos ir para a guia Dados, Obter dados externos e usar Do texto. Mas se você estiver no Office 365, a versão mais recente do Office 365, essa seção se foi. Tudo bem, então no Office 365 depois de clicar com o botão direito do mouse aqui e dizer Personalizar Barra de Ferramentas de Acesso Rápido e à esquerda escolher Todos os Comandos.
Agora, esta é uma lista muito longa, vamos descer para os F's. F's para From Text - veja todos esses From's, preciso encontrar aquele que diz From Text (Legacy). Essa é a versão antiga. Agora veja, eles querem que usemos o Power Query, mas vamos apenas criar algo que funcione para todos. Agora que eu fiz, agora que eu tenho From Text Legacy, irei aqui apenas para planilha totalmente nova, Inserir planilha. Agora temos um lugar para ir do texto e navegaremos para o nosso arquivo CSV. Clique em Importar e diremos Delimitado. Sim! Mas na etapa 2, direi que quero delimitá-lo na vírgula. Eu também quero Delimitá-lo no Espaço e queria Delimitá-lo em Alt + 0160. Agora, novamente, se você não tiver um teclado numérico, você 'terei de usar o truque que mostrei há alguns minutos para poder copiar e colar na célula. E oh! A propósito, se você tiver várias coisas próximas umas das outras, até mesmo uma vírgula e um Alt + 0160, trate esses delimitadores consecutivos como um só. Tudo bem, este Texto, na verdade todos esses, serão Texto. Não queremos que eles mexam com nenhum desses. Todos eles ficam assim.
Now, here's the beautiful thing. First off, CSV files, will get the answer to these questions because we use From Text and we get to say where we're going to put it and Properties, that we want to Save the query definition. And then, every time we open this file maybe we can go out and refresh the data, so this workbook could be the holder that every time we open this, it's going to go back out to the CSV and remember all of our answers and do that- do all the steps. So click Close, click OK and nothing comes in and scientific notation it's all been changed to Text. And you know, we don’t have to worry about just double-clicking they’ll be CSV file because it's allowing us to specify what each of those fields are.
Alright, my book, Power Excel with, has a lot of different tips: 617 Excel mystery solved. Now this one, unfortunately, is not in the book but it'll be in the next edition of the book, I guarantee that.
A lot of different things we talked about today. If you have spaces that TRIM won’t remove, learn how to figure this out. If you have a part number that ends in E and then a single digit or you have a partner with more than 15 digits, when you import a CSV file all of those are going to change to scientific notation. And oh by the way, if you've been burned by this, here's another URL I'm going to paste down in YouTube videos. Come out here and vote to make the Excel team try and tell them Excel team that you want Excel to stop changing large numbers to scientific notation. 584 votes right now. Let's try and get that up to 600, 700, 800 or even a thousand.
Alright, so I showed you how to change the extensions in Windows Explorer. When you open a .txt file, yeah, sure the things coming as text but as soon as you try and get rid of those non-breaking spaces, the part numbers change back to scientific notation and you have to swear again. So we use Data, Get External Data From Text, the old legacy version. If missing from Office 365 replaced by Get & Transform, so you have to right-click the Quick Access Toolbar and Customize in order to find it. Now when we open a CSV file with From Text, it lets you go through the text import wizard which is better than just double-clicking the CSV file. And step 2, the wizard will specify a comma and a space and then Alt+0160 as custom. Treat consecutive delimiters as one.
Este truque incrível do meu amigo Jan Karel, e não se esqueça de votar em excel.uservoice.com. Bem, ei, quero agradecer por você passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2087.xlsm
