I dag er et vanvittigt spørgsmål. Du har en kolonne med delnumre. Der er overalt fra 4 til 7 bindestreger i delnummeret. Du vil kun udtrække den del af delnummeret, der er fulgt efter det første bindestreg og op til, men ikke med det sidste bindestreg. Dette er en duellerende Excel-episode.
Se video
- Målet er at finde det første og sidste strejf og holde alt imellem
- Den svære del her er at finde den sidste bindestreg
- Bill Method 1: Flash Fill
- Udfyld manuelt de første par (inklusive nogle med forskelligt antal bindestreger)
- Vælg den tomme celle under det
- Ctrl + E til Flash-udfyldning
- Mike metode 2:
- Brug Power Query
- I Excel 2016 er Power Query i Get & Transform-gruppen i Excel 2016
- I Excel 2010 og 2013 skal du downloade Power Query fra Microsoft. Det opretter en ny fane Power Query i båndet
- Konverter dine data til en tabel ved hjælp af Ctrl + T
- Brug splitdata i strømforespørgsel - først for at opdele ved bindestreg til venstre, derefter for at opdele ved bindestreg til højre
- Regningsmetode 3:
- VBA-funktion, der gentager sig fra slutningen af cellen baglæns for at finde det sidste bindestreg
- Mike Metode 4:
- Brug SUBSTITUTE til at finde placeringen af Nth dash
- SUBSTITUTE er den eneste tekstfunktion, der giver dig mulighed for at specificere et instansnummer
- Brug for at finde hvilket instansnummer
=LEN(A2)-LEN(SUBSTITUTE)
Videoudskrift
Bill: Hej. Velkommen tilbage. Det er tid til en anden Dueling Excel podcast. Jeg er Bill Jelen fra MrExcel. (Jeg får følgeskab af Mike Girvin fra ExcelIsFun. Dette er vores - 00:03) episode 185: uddrag fra den første - til den sidste -.
I orden. Dagens spørgsmål sendes af Anvar på YouTube. Hvordan kan jeg udtrække alt fra det første - til det sidste - og tjekke disse data, han har her. Der er et stort antal bindestreger, hvor som helst fra 3, 5, 6, 7 bindestreger, okay?
Så min første tanke er, ja, hej, det er virkelig let at finde den første - ikke? = venstre eller = MIDDEN af FINDEN af A2 og derefter -, +1 okay, men for at komme til det sidste -, det får mig til at gøre ondt i hovedet, ikke, for, hvor mange bindestreger har vi? Vi kunne tage SUBSTITUTE af A2 og erstatte bindestregerne og sammenligne længden af den, den oprindelige længde. Det fortæller mig antallet af bindestreger, men nu ved jeg hvilke - der skal findes 2., 3., 4., 5., men bruger jeg FIND?
Jeg var klar til at gå til VBA, ikke? Det er min knæk-reaktion. Sagde jeg, vent et øjeblik. Jeg sagde, Anvar, hvilken version af Excel er du i? Han siger, jeg er i Excel 2016. Jeg sagde, det er smukt. Hvis du er i Excel 2013 eller nyere, kan vi bruge denne fantastiske nye funktion kaldet flashfyld. Med flashfyld skal vi bare give det et mønster, og jeg vil give det nok af et mønster, så det er ikke bare, at jeg tager en med to bindestreger og gør det et par gange. Jeg vil sørge for, at jeg har et par forskellige bindestreger på den måde. Tchad i Excel-teamet ved, hvad jeg leder efter. Tchad er den fyr, der skrev logikken til flashudfyldning. Så jeg får cirka 3 af dem derinde, og derefter er CONTROL + E genvejen til brug af DATA og derefter FLASH FILL, og det ser helt sikkert ud til, at det gjorde den rigtige ting. Okay, Mike.Lad os se, hvad du har.
Mike: Tak, MrExcel. Ja. Flashfyld vinder. Denne funktion lige der, flashfyld, er et af de moderne Excel-værktøjer, der simpelthen er forbløffende. Hvis det er en engangsaftale, og du har et ensartet mønster, hej, sådan ville jeg gøre det.
Hej, lad os gå over til næste ark. Nu, i stedet for at bruge flashfyld, kan vi faktisk bruge strømforespørgsel. Nu bruger jeg Excel 2016, så jeg har GET & TRANSFORM-gruppen. Det er strømforespørgsel. I tidligere versioner, 2013 (til 10 - 2:30), skal du faktisk downloade den gratis strømforespørgsel tilføjelse.
For at få strømforespørgsel til at fungere skal dette nu konverteres til en Excel-tabel. Nu igen ville jeg bruge flashfyld, hvis dette var en engangsaftale. Hvornår vil du bruge strømforespørgsel? Nå, hvis du havde rigtig store data eller kom fra en ekstern kilde, ville dette være vejen at gå, eller måske kan du endda lide dette bedre end at skulle skrive 3 eller 4 eksempler til flashudfyldning, fordi vi med strømforespørgsel kan sig specifikt find den første - og find den sidste -.
Nu skal jeg konvertere dette til en Excel-tabel. Jeg har valgt en enkelt celle, tomme celler hele vejen rundt. Jeg går til INSERT, TABLE, eller du bruger tastaturet, CONTROL + T. Jeg kan klikke på OK eller ENTER. Jeg vil navngive denne tabel, så jeg går op til TABELVÆRKTØJER, DESIGN, op i EJENDOMME. Jeg kalder dette STARTKEYTABLE og ENTER. Nu kan jeg gå tilbage til DATA, bringe det til strømforespørgsel ved hjælp af FROM TABLE-knappen. Der er min kolonne. Der er navnet. Jeg vil ikke beholde dette navn, fordi output vil blive eksporteret til Excel, og jeg vil give det et andet navn. Så jeg kalder det RENGØRT HÅNDBAR. Jeg har ikke brug for den ÆNDREDE TYPE. Jeg kigger bare på kilden. Nu kan jeg klikke på kolonnen, og lige op i HJEM er der SPLIT-knappen. Jeg kan sige SPLIT, AF DELIMITER. Ser ud til at det allerede er gættet. JEG'Jeg vil sige VENSTRE. Klik på OK.
Hvis jeg kigger herover, ser jeg ÆNDRET TYPE. Jeg har ikke brug for det, så jeg vil slippe af med det trin. Jeg har kun SPLIT COLUMN BY DELIMITER. Nu skal jeg gøre dette igen, men i stedet for at bruge SPLIT-knappen her oppe skal du højreklikke ned til SPLIT COLUMN, BY DELIMITER, og se på det. Vi kan vælge at opdele den med den HØJRE MESTE DELIMITER. Klik på OK. Nu har jeg ikke brug for disse to kolonner, så jeg skal højreklikke på den kolonne, jeg vil beholde, FJERN ANDRE KOLONNER. Jeg skal faktisk X denne ÆNDREDE TYPE ud. Det vil sige Er du sikker på, at du vil slette dette? Jeg vil sige, ja, SLET. Der er mine rene data.
Nu kan jeg komme op til CLOSE & LOAD. LUK & LAD TIL. Dette er den nye IMPORT-dialogboks. Det stod tidligere LAD TIL, men jeg vil indlæse det til et bord på et EKSISTERENDE ARBEJDSBLAD. Klik på knappen Skjul. Jeg skal vælge C1, fjerne kollaps, klikke på OK, og så går vi. Strømforespørgsel for at rense vores data og få netop de data, vi ønsker. I orden. Jeg kaster det tilbage til.
Bill: Der er pointen lige der, RIGHT-MOST DELIMITER i SPLIT COLUMN BY DELIMITER, en af de seje funktioner i strømforespørgsel. Det er fantastisk.
I orden. Min knæ-jerk-reaktion - VBA UDF (uforståelig - 05:34) virkelig let at lave VBA. Skift til ALT + F11. INDSÆT EN MODUL. Skriv denne kode i dette modul. Jeg skal (oprette en - 05:43) helt ny funktion, jeg kalder den MIDPART, og jeg sender den tekst, og hvad jeg skal gøre, er vil gå fra det sidste tegn i den celle fra længden af MYTEXT tilbage til 1, TRIN -1 og se på det tegn. Så, MYTEXT-MIDTEN, den variabel i, fortæller os, hvilket tegn vi ser på i længden på 1. Er det en -? Så snart jeg finder en -, vil jeg tage VENSTRE på MYTEXT startende ved karakter i - 1, så jeg slipper alt for det sidste - hele vejen ud, og så sørg for at jeg ikke går fortsæt med at lede efter flere bindestreger, EXIT FOR får mig ud af denne (uforståelige - 06:17) løkke,og derfra er den nemme del. Vi tager bare MYTEXT, starter ved MIDTEN af MYTEXT, (hvor jeg bruger - 06:26) brug funktionen FIND til at finde den første -, gå 1 mere end det, og returner det tilbage.
Så lad os gå tilbage, ALT + Q, for at vende tilbage til Excel. = MIDPART-fanen for det, og det ser ud til at det fungerer. Kopier det ned. Mike, har du en anden? (= MIDPart (A2))
Mike: Nå, jeg har en anden, men det bliver en lang formel - ikke så kort som den UDF. Okay, lad os gå over til næste ark. Hvis vi nu skal lave en formel, og vi har en tekst, og der altid er et andet antal afgrænsere, skal jeg på en eller anden måde få den sidste afgrænsers position.
Nu tager dette et par trin, men jeg begynder med SUBSTITUTE-funktionen. Jeg vil gennemgå den tekst, den gamle tekst, jeg vil finde, er i ”, det -, og hvad vil jeg sætte i stedet for eller erstatte det? "". Det vil ikke sætte noget i. Hvis jeg) og CONTROL + ENTER nu, hvad skal det så gøre? (= UDSKIFTER (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.
Nå, der går du. Jeg vil gerne takke alle for at komme forbi. Vi ses næste gang til en anden Dueling Excel podcast fra og ExcelIsFun.
Download fil
Download eksempelfilen her: Duel185.xlsm
