Hoje é uma pergunta maluca. Você tem uma coluna de números de peça. Existem de 4 a 7 traços no número da peça. Você deseja extrair apenas a parte do número da peça após o primeiro traço e até, mas não incluindo o último traço. Este é um episódio de duelo do Excel.
Assistir vídeo
- O objetivo é encontrar o primeiro e o último traço e manter tudo entre
- A parte difícil aqui é encontrar o último traço
- Método de Bill 1: Preenchimento Flash
- Preencha manualmente os primeiros (incluindo alguns com diferentes números de travessões)
- Selecione a célula em branco abaixo dessa
- Ctrl + E para Preencher Flash
- Método 2 de Mike:
- Use o Power Query
- No Excel 2016, o Power Query está no grupo Get & Transform no Excel 2016
- No Excel 2010 e 2013, baixe o Power Query da Microsoft. Ele cria uma nova guia do Power Query na Faixa de Opções
- Converta seus dados em uma tabela usando Ctrl + T
- Use a divisão de dados no Power Query - primeiro para dividir no traço mais à esquerda, depois para dividir no traço mais à direita
- Método de cobrança 3:
- Função VBA que itera do final da célula para trás para encontrar o último traço
- Método 4 de Mike:
- Use SUBSTITUTE para encontrar a localização do enésimo traço
- SUBSTITUTE é a única função de texto que permite especificar um número de instância
- Para encontrar qual número de instância, use
=LEN(A2)-LEN(SUBSTITUTE)
Transcrição de vídeo
Bill: Ei. Bem vindo de volta. É hora de outro podcast Dueling Excel. Sou Bill Jelen, do MrExcel. (Mike Girvin da ExcelIsFun vai se juntar a mim. Este é o nosso - 00:03) episódio 185: extrato do primeiro - ao último -.
Tudo bem. A pergunta de hoje é enviada por Anvar no YouTube. Como posso extrair tudo do primeiro - ao último -, e conferir esses dados que ele tem aqui. Há um grande número de traços, entre 3, 5, 6, 7 traços, certo?
Então, meu primeiro pensamento é, bem, ei, é realmente fácil encontrar o primeiro - certo? = esquerda ou = MEIO do FIND de A2 e então o -, +1 tudo bem, mas para chegar ao último -, isso vai fazer minha cabeça doer, certo, porque, bem, quantos traços nós temos? Poderíamos pegar o SUBSTITUTO de A2, substituindo os travessões, e comparar o comprimento daquele, o comprimento original. Isso me diz o número de travessões, mas agora eu sei quais - para encontrar, o 2º, 3º, 4º, 5º, mas devo usar FIND?
Eu estava pronto para ir para o VBA, certo? Essa é a minha reação automática. Eu disse, espere um segundo. Eu disse, Anvar, em que versão do Excel você está? Ele diz, estou no Excel 2016. Eu disse, isso é lindo. Se você estiver no Excel 2013 ou mais recente, poderíamos usar este ótimo novo recurso chamado preenchimento flash. Com o preenchimento instantâneo, só temos que dar a ele um padrão, e vou dar um padrão suficiente, então não é só pegar um com dois traços e fazer isso algumas vezes. Quero ter certeza de que tenho alguns traços diferentes dessa forma. Chad da equipe do Excel sabe o que estou procurando. Chad é o cara que escreveu a lógica para preenchimento instantâneo. Então, eu pego cerca de 3 deles e então CONTROL + E é o atalho para usar DATA e FLASH FILL, e, com certeza, parece que fez a coisa certa. Tudo bem, Mike.Vamos ver o que você tem.
Mike: Obrigado, MrExcel. Sim. O preenchimento do Flash vence. Esse recurso, preenchimento em flash, é uma das ferramentas modernas do Excel que é simplesmente incrível. Se for um acordo único e você tiver um padrão consistente, é assim que eu faria.
Ei, vamos passar para a próxima folha. Agora, em vez de usar o preenchimento flash, podemos realmente usar consulta avançada. Agora, estou usando o Excel 2016, então tenho o grupo GET & TRANSFORM. Isso é consulta de poder. Em versões anteriores, 2013 (para 10 - 2:30), você realmente tem que baixar o suplemento gratuito de consulta de energia.
Agora, para fazer a consulta avançada funcionar, ela deve ser convertida em uma tabela do Excel. Agora, novamente, eu usaria o preenchimento flash se este fosse um negócio único. Quando você usaria a consulta avançada? Bem, se você tivesse realmente um grande volume de dados ou estivesse vindo de uma fonte externa, este seria o caminho a percorrer, ou você pode até gostar mais disso do que ter que digitar 3 ou 4 exemplos para preenchimento flash porque, com consulta avançada, podemos diga especificamente encontre o primeiro - e encontre o último -.
Agora, vou converter isso para uma tabela do Excel. Eu tenho uma única célula selecionada, células vazias ao redor. Vou para INSERT, TABLE, ou você usa o teclado, CONTROL + T. Posso clicar em OK ou ENTER. Eu quero nomear esta tabela, então irei até TABLE TOOLS, DESIGN, até PROPERTIES. Vou chamar isso de STARTKEYTABLE e ENTER. Agora posso voltar a DATA, colocá-lo em consulta avançada usando o botão DA TABELA. Essa é minha coluna. Aqui está o nome. Não quero manter esse nome porque a saída será exportada para o Excel e quero dar a ela um nome diferente. Então, vou chamá-lo de CLEANEDKEYTABLE. Eu não preciso desse TIPO ALTERADO. Estou apenas olhando para a fonte. Agora posso clicar na coluna e, logo no início, há o botão SPLIT. Posso dizer SPLIT, BY DELIMITER. Parece que já adivinhou. EU'vou dizer LEFT-MOST. Clique OK.
Agora, se eu olhar aqui, vejo TIPO ALTERADO. Eu não preciso disso, então vou me livrar dessa etapa. Tenho apenas COLUNA DIVIDIDA POR DELIMITER. Agora, vou fazer isso de novo, mas, em vez de usar o botão SPLIT aqui em cima, clique com o botão direito em SPLIT COLUMN, BY DELIMITER, e veja isso. Podemos escolher dividi-lo pelo DELIMITER MAIS CERTO. Clique OK. Agora, não preciso dessas duas colunas, então vou clicar com o botão direito na coluna que desejo manter, REMOVER OUTRAS COLUNAS. Na verdade, estou indo para X este TIPO ALTERADO. Vai dizer TEM CERTEZA DE QUE DESEJA EXCLUIR ISTO? Eu vou dizer, sim, DELETE. Aqui estão meus dados limpos.
Agora posso subir para CLOSE & LOAD. FECHE E CARREGUE PARA. Esta é a nova caixa de diálogo IMPORT. Costumava dizer LOAD TO, mas quero carregá-lo em uma mesa, em uma PLANILHA EXISTENTE. Clique no botão recolher. Vou selecionar C1, cancelar o recolhimento, clicar em OK e pronto. Power query para limpar nossos dados e obter apenas os dados que queremos. Tudo bem. Vou jogar de volta para.
Bill: Aí está o ponto aqui, RIGHT-MAIS DELIMITER na SPLIT COLUMN BY DELIMITER, um dos recursos interessantes em power query. Fantástico.
Tudo bem. Minha reação automática - VBA UDF (ininteligível - 05:34) realmente fácil de fazer VBA. Mude para ALT + F11. INSERIR um MÓDULO. Nesse módulo, digite este código. Vou (criar uma - 05:43) função totalmente nova, vou chamá-la de MIDPART e vou passar um texto para ela, e então o que farei é indo do último caractere nessa célula do comprimento de MYTEXT de volta para 1, PASSO -1 e olhe para esse caractere. Então, o MID do MYTEXT, essa variável i, nos diz qual caractere estamos olhando para o comprimento de 1. É um -? Assim que eu encontrar um -, vou pegar a ESQUERDA do MYTEXT começando no caractere i - 1, então eu me livro de tudo para o último - até o fim, e então, certifique-se de não ir continue procurando por mais travessões, o EXIT FOR me tirará desse loop (ininteligível - 06:17),e a partir daí vem a parte fácil. Vamos apenas pegar o MYTEXT, começar no MEIO do MYTEXT, (onde eu uso o - 06:26) usar a função FIND para encontrar o primeiro -, vá 1 a mais que isso e retorne-o de volta.
Então, vamos voltar, ALT + Q, para voltar ao Excel. = Guia MIDPART disso, e parece que está funcionando. Copie isso. Mike, você tem outro? (= MIDPart (A2))
Mike: Bem, eu tenho outro, mas será uma fórmula longa - não tão curta quanto aquele UDF. Tudo bem, vamos passar para a próxima página. Agora, se vamos fazer uma fórmula e temos algum texto e sempre há um número diferente de delimitadores, de alguma forma, eu preciso obter a posição desse último delimitador.
Agora, isso vai levar alguns passos, mas vou começar com a função SUBSTITUTE. Vou examinar esse texto, o texto antigo que desejo encontrar está em ”, que -,, e o que desejo colocar em seu lugar ou substituir? “”. Isso não adicionará nada. Agora, se eu) e CONTROL + ENTER, o que isso vai fazer? (= SUBSTITUIR (A2, “-”, “”))
Well, now I can take the length of this and subtract it from the length of this item. That will tell me how many delimiters there are. F2, and right at the beginning, I'm going to type the length of that. That will give me the full length - the length of that dashless text, ), CONTROL+ENTER, double click, and send it down. that tells me how many delimiters there are for this text. There are 6. (=LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))
Now I'm going to use that sixth now inside of substitute to put a different character right at the sixth listing of the delimiter, F2, and if I type SUBSTITUTE, what we want to notice is this function has an instance number. If you look at other text functions like search and find, they don't have an instance number. Substitute is the only one I can think of that actually lets you specifically say which instance of a delimiter you want to deal with. Here's the text, ,. Old text is in “ a -, and I need to pick for the new text some character that will never be in this text ring. I'm going to choose, like, or something like that, , and that's where instance number comes in, ), CONTROL+ENTER, and there it is. If I double click and send it down, it's always putting that in the position of the last delimiter. (=SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))
Now I need to figure out, in each one of these, what position it is in. F2. I'm going to use the SEARCH function. SEARCH. I type S and tab. Now, search and find are the same except for search is not case-sensitive. In this case, either one would be fine because the text I'm looking for is in “, that ^, ”, , within that text. By the way, the reason that I use search instead of find is because S tab gets me search but F I tab will get me find. So, it's like one character less when typing it out. CONTROL+ENTER, double click and send it down, and now it tells me, in the 27th position is that last delimiter. (=SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))))
Now, I'm going to take this approach for these text items. I'm now going to use the left function and get everything from the very beginning all the way up to that position. That will get rid of that last little bit. Now, actually, search tells us 27 which is right there and we only want to go to 26. So, F2, and, at the end, I'm going to - 1, CONTROL+ENTER, double click and send it down. Now, I can use the left function. F2. LEFT. There it is, left of that, ,. That's how many characters. ), CONTROL+ENTER, double click and send it down. So, now, we have gotten rid of the last little bit after the last delimiter in every cell. (=LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1))
Now all I need to do is replace the first four characters, first four characters, first three characters. Now, I can use the search function on the original text because it can find the - which is three and I'll tell replace, please go, from the first character, three characters in and replace it with nothing, F2, and right at the beginning, I'm going to type REPLACE. There's the old text. Now watch this. I want to give myself a little bit more breathing room. I'm just going to artificially pick a space, ALT+ENTER. That's kind of like we do in DAX. Now I just have more breathing room. That's the old text, ,. The starting number, I need to always start at the first position so I simply type 1, , and I need to find that first - which represents number of characters. So, S tab, “-” , through… within that text, that search will find 4, 4, 3. That will work. ) and then , new text “”. That will put nothing in those first characters. ). I have the entire column highlighted so I can populate this edited formula with CONTROL+ENTER, and there we go. All the way down, we’re extracting everything between the first and the last -. (=REPLACE(LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1),1,SEARCH(“-”,A2),“”))
Now, the only reason we want to be crazy like that with formulas is if we wanted the formula result to instantly update whenever we changed anything, so if I type -00, instantly it updates. Power query and flash fill will not automatically update, alright? Send it back to.
Bill: Well, that was one heck of a formula. Like, substitute was the trick. I had used substitute in the first step but didn't see that it had the instance number. Alright, so, we have four different methods here today. My first method is flash fill. Select first few, select the blank box below that, and then CONTROL+E to flash fill. Mike's method, use power query. I love that, especially the split data letting you use the leftmost - and then the rightmost -. My live seminars always talk about this one feature. Should be a finalist for the Nobel Prize for the best excel feature. It wouldn't win but it would be in one of the top five, I'm sure. My method number three, VBA function, a UDF user-defined function, that iterates from the end of the cell, and then, Mike's method, the awesome formula method. Use substitute to find the location of the nth - and then pass that answer back into substitute that tells you which instance number to look from. Brilliant.
Bem, aí está. Quero agradecer a todos pela visita. Nos vemos na próxima vez para outro podcast Dueling Excel de e ExcelIsFun.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Duel185.xlsm
