Idag är en galen fråga. Du har en kolumn med artikelnummer. Det finns allt från 4 till 7 streck i artikelnumret. Du vill endast extrahera delen av artikelnumret efter första strecket och upp till men inte inklusive det sista strecket. Detta är en duellerande Excel-episod.
Kolla på video
- Målet är att hitta det första och sista strecket och hålla allt däremellan
- Den svåra delen här är att hitta det sista strecket
- Räkningsmetod 1: Flashfyllning
- Fyll i de första paren manuellt (inklusive några med olika antal bindestreck)
- Välj den tomma cellen under det
- Ctrl + E till Flash Fill
- Mike Metod 2:
- Använd Power Query
- I Excel 2016 finns Power Query i gruppen Get & Transform i Excel 2016
- Ladda ner Power Query i Microsoft 2010 och 2013. Det skapar en ny flik Power Query i menyfliksområdet
- Konvertera dina data till en tabell med Ctrl + T
- Använd delad data i Power Query - först för att dela längst till vänster och sedan för att dela längst till höger
- Bill Method 3:
- VBA-funktion som itererar från slutet av cellen bakåt för att hitta det sista strecket
- Mike Metod 4:
- Använd SUBSTITUTE för att hitta platsen för Nth-strecket
- SUBSTITUTE är den enda textfunktionen som låter dig ange ett instansnummer
- För att hitta vilket instansnummer, använd
=LEN(A2)-LEN(SUBSTITUTE)
Videoutskrift
Bill: Hej. Välkommen tillbaka. Det är dags för ytterligare en Duelling Excel-podcast. Jag heter Bill Jelen från MrExcel. (Jag får sällskap av Mike Girvin från ExcelIsFun. Detta är vårt - 00:03) avsnitt 185: utdrag från det första - till det sista -.
OK. Dagens fråga skickas in av Anvar på YouTube. Hur kan jag extrahera allt från det första - till det sista - och kolla in de här uppgifterna han har här. Det finns ett stort antal streck, var som helst från 3, 5, 6, 7 streck, okej?
Så min första tanke är, ja, hej, det är verkligen lätt att hitta den första - eller hur? = vänster eller = MIDT i FINN av A2 och sedan -, +1 okej, men för att komma till det sista - kommer det att göra mitt huvud ont, eller hur, hur många streck har vi? Vi kan ta SUBSTITUTE för A2, byta ut streck och jämföra längden på den, den ursprungliga längden. Det talar om för mig antalet streck, men nu vet jag vilka - att hitta, 2: a, 3: e, 4: e, 5: e, men använder jag HITTA?
Jag var redo att gå till VBA, eller hur? Det är min knäskakreaktion. Sa jag, vänta en sekund. Jag sa, Anvar, vilken version av Excel är du i? Han säger, jag är i Excel 2016. Jag sa, det är vackert. Om du är i Excel 2013 eller senare kan vi använda den här fantastiska nya funktionen som kallas flash fill. Med flashfyllning måste vi bara ge det ett mönster, och jag kommer att ge det nog av ett mönster så det är inte bara att jag tar en med två streck och gör det ett par gånger. Jag vill se till att jag har några olika streck på det sättet. Tchad i Excel-teamet vet vad jag letar efter. Tchad är killen som skrev logiken för flashfyllning. Så jag får ungefär 3 av dem där inne och då är CONTROL + E genvägen för att använda DATA och sedan FLASH FILL, och det ser ut som om det gjorde rätt. Okej, Mike.Låt oss se vad du har.
Mike: Tack, MrExcel. Ja. Flashfyllning vinner. Den funktionen där, flashfyllning, är ett av de moderna Excel-verktygen som helt enkelt är fantastiskt. Om det är en engångsavtal och du har ett konsekvent mönster, hej, så skulle jag göra det.
Hej, låt oss gå vidare till nästa ark. Nu, istället för att använda flashfyllning, kan vi faktiskt använda strömfråga. Nu använder jag Excel 2016 så jag har gruppen GET & TRANSFORM. Det är maktfråga. I tidigare versioner, 2013 (till 10 - 2:30), måste du faktiskt ladda ner det gratis kraftfrågetillägget.
För att få strömförfrågan att fungera måste den nu konverteras till en Excel-tabell. Nu, igen, skulle jag använda flash fill om detta var en engångsavtal. När skulle du använda strömfråga? Tja, om du hade riktigt stora data eller kom från en extern källa, skulle det här vara vägen att gå, eller du kanske till och med gillar det här bättre än att behöva skriva 3 eller 4 exempel för blixtfyllning eftersom vi med strömfråga kan säg specifikt hitta den första - och hitta den sista -.
Nu ska jag konvertera detta till en Excel-tabell. Jag har valt en enda cell, tomma celler hela vägen. Jag går till INSERT, TABLE, eller så använder du tangentbordet, CONTROL + T. Jag kan klicka på OK eller RETUR. Jag vill namnge den här tabellen, så jag går upp till TABELLVERKTYG, DESIGN, upp till EGENDOM. Jag ska kalla detta STARTKEYTABLE och ENTER. Nu kan jag gå tillbaka till DATA, sätta den i strömförfrågan med knappen FROM TABLE. Det är min kolumn. Det är namnet. Jag vill inte behålla det här namnet eftersom utdata exporteras till Excel och jag vill ge det ett annat namn. Så jag kommer att kalla det RENGÖRT. Jag behöver inte den ÄNDRADE TYPEN. Jag tittar bara på källan. Nu kan jag klicka på kolumnen och precis uppe i HEM finns SPLIT-knappen. Jag kan säga SPLIT, AV DELIMITER. Ser ut som det redan gissat. Jag 'jag ska säga VÄNSTER-MEST. Klicka på OK.
Om jag tittar här ser jag FÖRÄNDRAD TYP. Jag behöver inte det så jag ska bli av med det steget. Jag har bara SPLIT COLUMN BY DELIMITER. Nu ska jag göra det här igen, men istället för att använda SPLIT-knappen här uppe, högerklicka ner till SPLIT COLUMN, BY DELIMITER, och titta på det. Vi kan välja att dela upp den med den HÖGERSTE DELIMITERaren. Klicka på OK. Nu behöver jag inte de här två kolumnerna, så jag kommer att högerklicka på den kolumn jag vill behålla, ta bort andra kolumner. Jag ska faktiskt X den här ÄNDRADE TYPEN. Det kommer att säga Är du säker på att du vill radera det här? Jag ska säga, ja, RADERA. Det finns mina rena data.
Nu kan jag komma upp till STÄNG & LADA. STÄNG & LADDA TILL. Detta är den nya IMPORT-dialogrutan. Det stod tidigare LADA TILL men jag vill ladda det till ett bord på ett befintligt ARBETSBLAD. Klicka på kollaps-knappen. Jag ska välja C1, rensa bort, klicka på OK och så går vi. Kraftfråga för att städa våra data och få precis den information vi vill ha. OK. Jag kastar tillbaka det till.
Bill: Det är poängen där, HÖGERSTA DELIMITERARE i SPLIT COLUMN BY DELIMITER, en av de coola funktionerna i power query. Det är jättebra.
OK. Min knäströmsreaktion - VBA UDF (oförståelig - 05:34) riktigt lätt att göra VBA. Byt till ALT + F11. INSÄTTA en MODUL. Skriv den här koden i den modulen. Jag ska (skapa en - 05:43) helt ny funktion, jag kommer att kalla den MIDPART, och jag kommer att skicka lite text till den, och vad jag ska göra är att jag är kommer att gå från den sista karaktären i den cellen från längden på MYTEXT tillbaka till 1, STEG -1 och titta på den karaktären. Så, MYTEXT MID, den variabeln i, berättar vilken karaktär vi tittar på för längden på 1. Är det en -? Så snart jag hittar en - kommer jag att ta VÄNSTER till MYTEXT från och med karaktär i - 1, så jag blir av med allt för det sista - hela vägen ut, och se till att jag inte går fortsätt leta efter fler streck, EXIT FOR kommer att få mig ur den här (oförståelig - 06:17) slingan,och därifrån är den enkla delen. Vi ska bara ta MYTEXT, börja vid MYTEXT MID, (där jag använder - 06:26) använd funktionen HITTA för att hitta den första -, gå 1 mer än så, och returnera den tillbaka.
Så, låt oss gå tillbaka, ALT + Q, för att återgå till Excel. = MIDPART-fliken för det, och det ser ut som om det fungerar. Kopiera ner det. Mike, har du en till? (= MIDPart (A2))
Mike: Ja, jag har en annan, men det kommer att bli en lång formel - inte så kort som den UDF. Okej, låt oss gå vidare till nästa ark. Om vi ska göra en formel och vi har lite text och det alltid finns ett annat antal avgränsare, måste jag på något sätt få positionen för den sista avgränsaren.
Nu kommer det att ta några steg men jag ska börja med SUBSTITUTE-funktionen. Jag ska titta igenom den texten,, den gamla texten jag vill hitta är i ”, det -, och vad vill jag lägga i stället för eller ersätta det? "". Det kommer ingenting att göra. Om jag) och CONTROL + RETUR, vad ska det göra? (= BYTARE (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.
Tja, där går du. Jag vill tacka alla för att de kom förbi. Vi ses nästa gång för en annan Dueling Excel-podcast från och ExcelIsFun.
Nedladdning fil
Ladda ner exempelfilen här: Duel185.xlsm
