Como separar uma coluna de dados do Excel em duas colunas. Como analisar dados no Excel.
Assistir vídeo
- O primeiro método de Bill usando Text to Columns (encontrado na guia Dados).
- Na etapa 1, escolha delimitado. Na etapa 2, escolha um espaço. Pule o passo 3 clicando em terminar.
- O texto será dividido em cada espaço, então qualquer coisa com três palavras acabará em 3 células. Junte-os novamente com
=TEXTJOIN(" ",True,B2:E2)ou - com
=B2&" "&C2&" "&D2 - O primeiro método de Mike usa o Power Query. O Power Query é Get & Transform em 2016 ou um download gratuito para 2010 ou 2013.
- Primeiro, converta seus dados em uma tabela usando Ctrl + T. Em seguida, no Power Query, em Tabela. Split Column, da Delimiter. Selecione Espaço e, em seguida, no delimitador mais à esquerda.
- Você pode renomear uma coluna clicando duas vezes!
- Fechar e carregar para … e escolher um novo local na planilha.
- O segundo método de Bill é usar o Flash Fill. Digite novos títulos em A, B e C. O Flash Fill não funcionará se você não tiver títulos! Digite um padrão para as duas primeiras linhas.
- Vá para a primeira célula em branco em B e pressione Ctrl + E. Repita para a coluna C.
- O segundo método de Mike é usar estas fórmulas:
- Para a primeira parte, use
=LEFT(A2,SEARCH(" ",A2)-1) - Para a segunda parte, use
=SUBSTITUTE(A2,B2&" ","")
Transcrição de vídeo
(Música)
Bill Jelen: Ei, bem-vindo de volta, é hora de outro podcast de duelo do Excel. Sou Bill Jelen de. Terei a companhia de Mike Girvin, do Excel Is Fun. Este é o nosso
Episódio 182: Dividir dados de uma célula para aparecer em duas células.
Certo, a pergunta de hoje foi enviada por Tom. Existe uma maneira de dividir facilmente os dados em uma célula para que os dados apareçam em duas células? Por exemplo, 123 Main Street, ele quer 123 em uma célula e Main Street em outra; ou Howard e Howard e depois Fim. Passei inúmeras horas separando esse tipo de dados. Eu gostaria de ouvir de sua empresa, embora haja muitas maneiras diferentes de fazer isso.
A primeira coisa que vou fazer é selecionar todos os Dados, Ctrl + Shift + Seta para baixo e, em seguida, Dados, Texto para colunas. Texto para colunas na etapa 1, os dados são delimitados. Ele é delimitado por um Espaço e, a seguir, basta clicar em Concluir. Agora, aqui está o problema com este método é que se você tem 123 Main Street vai acabar em 3 células em vez de 2 células. Oh, o Power Query tornaria isso muito mais fácil, mas aqui estamos. Tudo bem, então o que vou fazer é sair bem à direita dos Dados, onde sei que além de onde tudo é construído. Se eu estiver no Office 365, usarei o TEXTJOIN. TEXTJOIN, aquela coisa incrível, delimitador é um Espaço. Ignore as células vazias True e, em seguida, as células que desejo concatenar assim, e apenas copio todas elas, Ctrl + V. Vou copiar Ctrl + C e, em seguida, Home, Paste,Cole como valores e, neste ponto, posso excluir essas 3 colunas extras.
Ahh, mas ninguém tem o Office 365, certo? Então, se você não tem o Office 365, você tem que fazer = esta coisa e “” e aquilo, e então se houver mais “” e aquilo, e se houver mais, continue. Nesse caso, é inútil porque não há nada acabado em D, mas essa é a ideia. Ctrl + C, copie-o para a última linha de dados, Ctrl + V e depois Ctrl + C, Alt + ESV para criar esses valores B. E aí estamos nós, certo. Mike, vamos ver o que você tem.
Mike Girvin: Obrigado. Ei, você me lançou um fácil aqui porque você já mencionou Get & Transform Power Query, o antigo Text to Columns só permite que você diga um espaço em cada caractere, certo? Bem, se usarmos o Power Query, podemos usar esse delimitador e dizer: “Ei, apenas divida na primeira ocorrência”.
Agora, para colocar esses dados no Editor de Consultas, temos que convertê-los em uma tabela Excel. Então subo para Inserir, Tabela ou uso Ctrl + T. Minha tabela tem cabeçalhos, o botão OK está destacado para que eu possa clicar nele com o mouse ou apenas pressionar Enter. Agora eu quero nomear esta Tabela, então vou subir aqui, OriginalData e Enter. Agora, esta é uma tabela Excel, podemos ir até os Dados e aí está a Da Tabela. Isso o trará do Excel para o Editor. A coluna é selecionada: Guia Home Ribbon, podemos dizer Split Column by Delimiter ou venha até aqui e clique com o botão direito, Split Column by Delimiter. Na lista suspensa, podemos dizer, ei, use um Espaço e observe este delimitador mais à esquerda. Quando eu clicar em OK, BOOM! Aí está. Agora, vou nomear essas duas colunas: clique duas vezes em Parte 1 Enter, clique duas vezes em Parte 2 e Enter. Agora,Posso subir aqui ou Close & Load, Close & Load To e posso escolher onde colocar isso. Definitivamente, quero despejá-lo como uma tabela, nova planilha, planilha existente. Destaque isso e clique no botão recolher. Vou dizer D1, clique em OK e em Carregar. E aí vamos nós, nosso Power Query Output.
Tudo bem, jogue de volta para.
Bill Jelen: Oh, Mike, o Power Query é incrível! Sim, é uma ótima maneira de ir. Aqui está outro que pode funcionar se você tiver o Excel 2013 ou mais recente.
E o que vamos fazer é vir aqui e dizer Primeira Parte e depois Segunda Parte. Certifique-se de colocar esses cabeçalhos de forma que, se você não colocar esses cabeçalhos, eles não precisam ser assim, mas precisam ter cabeçalhos ou não vai funcionar. Vou colocar 123 com a Main Street e depois Howard e End, assim. Agora que temos um pequeno padrão legal ali, saia aqui na Guia de dados e no Flash Fill, que é Ctrl + E, pressione Ctrl + E ali e pressione Ctrl + E ali. O bonito é que não temos que concatenar dados como no meu exemplo. Certo, Mike, de volta para você.
Mike Girvin: Ding-ding-ding. Esse é o vencedor, sem dúvida. Flash Fill é o caminho a percorrer. Observe, não tivemos que convertê-lo em uma tabela ou abrir qualquer caixa de diálogo; apenas digitei alguns exemplos e depois Ctrl + E.
Tudo bem, poderíamos fazer isso com fórmulas, embora o Flash Fill provavelmente fosse mais rápido. Bem, olhe para isso, o padrão assim como esta célula da lista usada no Flash Fill é tudo antes do primeiro espaço e tudo depois. Então, vou usar a função ESQUERDA, o Texto está aí e quantos caracteres à esquerda? Bem, vou procurar esse espaço - 1 2 3 4 usando a função SEARCH, Find Text, space e “”, dentro dele. Agora, observe que a Pesquisa contaria em seus dedos 1 2 3 4 e isso iria chegar ao espaço que eu quero, aquele espaço então I -1) Ctrl + Enter, clique duas vezes e envie para baixo. Então, isso sempre coloca tudo antes do primeiro espaço.
Agora, observe que já temos o texto aqui para que eu possa usar a função SUBSTITUTE. O texto que vou examinar são os dados completos, vírgula, o texto antigo que desejo procurar e, em seguida, SUBSTITUIR. Nada é quase 1 2 3. Na verdade, quero adicionar o Espaço que acabei de retirar na fórmula anterior, de volta. Agora, ele procurará 1 2 3, Espaço e depois Howard, Espaço e assim por diante, Vírgula e depois o novo texto que eu quero substituir. Bem, para dizer a SUBSTITUTE que você deseja substituí-lo por nada, você diz “” sem espaço entre eles, feche parênteses e isso vai funcionar. Ctrl + Enter, clique duas vezes e envie para baixo. Tudo bem? Apenas jogue de volta para.
Bill Jelen: Ei! Tudo bem, Mike, os dois métodos foram incríveis. Vamos fazer um resumo rápido aqui. Meu primeiro método usando Text to Columns: Etapa 1, escolha Delimitado; Etapa 2, escolha um espaço e clique em Concluir. O problema é que, se você tiver vários espaços, ele acabará em várias células. Eu tenho que colocá-los novamente. Office 365 TEXTJOIN ou o antigo B2 & “” e C2 e assim por diante.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking. I've been right-clicking and renaming all this time and being a little annoyed of that. That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer. Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing. That was awesome.
Tudo bem, quero agradecer a todos pela visita. Nos vemos na próxima vez em outro Podcast Dueling Excel do e Excel is Fun.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Duel182.xlsm
