I dag er et sprøtt spørsmål. Du har en kolonne med delenumre. Det er alt fra 4 til 7 streker i delenummeret. Du vil bare trekke ut delen av delenummeret etter første dash og opp til men ikke inkludert siste dash. Dette er en duellerende Excel-episode.
Se på video
- Målet er å finne den første og siste streken og holde alt imellom
- Den vanskelige delen her er å finne den siste streken
- Regningsmetode 1: Flash Fill
- Fyll ut de første få manuelt (inkludert noen med forskjellig antall bindestreker)
- Velg den tomme cellen under den
- Ctrl + E til Flash Fill
- Mike Metode 2:
- Bruk Power Query
- I Excel 2016 er Power Query i Get & Transform-gruppen i Excel 2016
- Last ned Power Query fra Microsoft i Excel 2010 og 2013. Det oppretter en ny Power Query-fane i båndet
- Konverter dataene dine til en tabell ved hjelp av Ctrl + T.
- Bruk Split Data i Power Query - først å dele på venstre dashbord, deretter å dele på dashbordet til høyre
- Regningsmetode 3:
- VBA-funksjon som gjentas fra slutten av cellen bakover for å finne den siste streken
- Mike Metode 4:
- Bruk SUBSTITUTE for å finne plasseringen til Nth dash
- SUBSTITUTE er den eneste tekstfunksjonen som lar deg spesifisere et forekomstnummer
- Bruk for å finne hvilket forekomstnummer
=LEN(A2)-LEN(SUBSTITUTE)
Videoutskrift
Bill: Hei. Velkommen tilbake. Det er på tide med nok en Dueling Excel-podcast. Jeg er Bill Jelen fra MrExcel. (Jeg får følge av Mike Girvin fra ExcelIsFun. Dette er vår - 00:03) episode 185: utdrag fra den første - til den siste -.
Ok. Dagens spørsmål sendes inn av Anvar på YouTube. Hvordan kan jeg trekke ut alt fra det første til det siste, og sjekke ut disse dataene han har her. Det er et stort antall bindestreker, hvor som helst fra 3, 5, 6, 7 bindestreker, ok?
Så, min første tanke er, vel, hei, det er veldig lett å finne den første - ikke sant? = venstre eller = MIDT for FINN av A2 og deretter -, +1 ok, men for å komme til det siste -, det kommer til å gjøre hodet mitt vondt, ikke sant, fordi, vel, hvor mange bindestreker har vi? Vi kan ta SUBSTITUTE av A2, erstatte bindestrekene, og sammenligne lengden på den, den opprinnelige lengden. Det forteller meg antall bindestreker, men nå vet jeg hvilke - å finne, 2., 3., 4., 5., men bruker jeg FINN?
Jeg var klar til å gå til VBA, ikke sant? Det er min knebøy reaksjon. Jeg sa, vent litt. Jeg sa, Anvar, hvilken versjon av Excel er du i? Han sier, jeg er i Excel 2016. Jeg sa, det er vakkert. Hvis du er i Excel 2013 eller nyere, kan vi bruke denne flotte nye funksjonen kalt flash fill. Med flash fill må vi bare gi det et mønster, og jeg skal gi det nok av et mønster, så det er ikke bare at jeg tar en med to bindestreker og gjør det et par ganger. Jeg vil sørge for at jeg har noen forskjellige bindestreker på den måten. Tsjad på Excel-teamet vet hva jeg ser etter. Chad er fyren som skrev logikken for flash fill. Så jeg får omtrent 3 av dem der inne, og da er CONTROL + E snarveien for å bruke DATA og deretter FLASH FILL, og det ser ut til at det gjorde det rette. Greit, Mike.La oss se hva du har.
Mike: Takk, MrExcel. Ja. Flash-fyll vinner. Den funksjonen der, flash fill, er et av de moderne Excel-verktøyene som rett og slett er utrolig. Hvis det er en engangsavtale og du har et konsistent mønster, hei, det er slik jeg ville gjort det.
Hei, la oss gå over til neste ark. Nå, i stedet for å bruke flash fill, kan vi faktisk bruke strømspørring. Nå bruker jeg Excel 2016, så jeg har GET & TRANSFORM-gruppen. Det er strømspørsmål. I tidligere versjoner, 2013 (til 10 - 2:30), må du faktisk laste ned det gratis strømspørringstillegget.
Nå, for å få strømforespørsel til å fungere, må dette konverteres til en Excel-tabell. Nå, igjen, vil jeg bruke flash fill hvis dette var en engangsavtale. Når vil du bruke strømspørsmål? Vel, hvis du hadde veldig store data eller kom fra en ekstern kilde, ville dette være veien å gå, eller kanskje du til og med liker dette bedre enn å måtte skrive 3 eller 4 eksempler for flash-utfylling fordi vi med strømspørsmål kan spesifikt si finn den første - og finn den siste -.
Nå skal jeg konvertere dette til en Excel-tabell. Jeg har valgt en enkelt celle, tomme celler hele veien rundt. Jeg går til INSERT, TABLE, eller du bruker tastaturet, CONTROL + T. Jeg kan klikke OK eller ENTER. Jeg vil nevne denne tabellen, så jeg går opp til TABELVERKTØY, DESIGN, opp i EGENSKAPER. Jeg skal kalle dette STARTKEYTABLE og ENTER. Nå kan jeg gå tilbake til DATA, bringe det til strømspørring ved å bruke FROM TABLE-knappen. Det er kolonnen min. Det er navnet. Jeg vil ikke beholde dette navnet fordi utdataene blir eksportert til Excel, og jeg vil gi det et annet navn. Så, jeg vil kalle det RENGJØRT. Jeg trenger ikke den ENDRE TYPEN. Jeg ser bare på kilden. Nå kan jeg klikke på kolonnen, og rett opp i HJEM er det SPLIT-knappen. Jeg kan si SPLIT, AV DELIMITER. Ser ut som det allerede gjettet. JEG'Jeg kommer til å si VENSTRE. Klikk OK.
Nå, hvis jeg ser over her, ser jeg ENDRET TYPE. Jeg trenger ikke det, så jeg skal bli kvitt det trinnet. Jeg har bare SPLITT KOLONNE AV DELIMITER. Nå skal jeg gjøre dette igjen, men i stedet for å bruke SPLIT-knappen her oppe, høyreklikker du ned til SPLIT KOLONN, AV DELIMITER, og ser på det. Vi kan velge å dele den med RIKTIGSTE DELIMITERER. Klikk OK. Nå trenger jeg ikke disse to kolonnene, så jeg skal høyreklikke på kolonnen jeg vil beholde, FJERN andre kolonner. Jeg skal faktisk X denne ENDRE TYPEN. Det kommer til å si ER DU SIKKER PÅ AT DU VIL SLETTE DETTE? Jeg skal si, ja, SLETT. Det er mine rene data.
Nå kan jeg komme opp til CLOSE & LOAD. Lukk og last til. Dette er den nye IMPORT-dialogboksen. Det pleide å si LOAD TO, men jeg vil laste det til et bord på et EKSISTERENDE ARBEIDSBLAD. Klikk på skjul-knappen. Jeg skal velge C1, fjerne kollaps, klikke OK, og så går vi. Strømspørring for å rense dataene våre og få akkurat de dataene vi ønsker. Ok. Jeg skal kaste den tilbake til.
Bill: Det er poenget der, RIKTIGSTE DELIMITER i SPLIT COLUMN BY DELIMITER, en av de kule funksjonene i strømspørsmål. Det er kult.
Ok. Min knebøyreaksjon - VBA UDF (uforståelig - 05:34) veldig lett å gjøre VBA. Bytt til ALT + F11. Sett inn en modul. I denne modulen skriver du inn denne koden. Jeg skal (opprette en - 05:43) helt ny funksjon, jeg skal kalle den MIDPART, og jeg skal gi den litt tekst, og det jeg skal gjøre er at jeg kommer til å gå fra det siste tegnet i den cellen fra lengden på MYTEXT tilbake til 1, TRINN -1 og se på tegnet. Så, MIDTEN til MYTEXT, den variabelen i, forteller oss hvilken karakter vi ser på for lengden på 1. Er det en -? Så snart jeg finner en -, skal jeg ta VENSTRE på MYTEXT fra og med karakter i - 1, så jeg blir kvitt alt for det siste - helt ut, og sørg for at jeg ikke går fortsett å lete etter flere bindestreker, EXIT FOR vil få meg ut av denne (uforståelige - 06:17) sløyfen,og derfra er den enkle delen. Vi skal bare ta MYTEXT, starte ved MIDTEN på MYTEXT, (der jeg bruker - 06:26), bruk funksjonen FINN for å finne den første -, gå 1 mer enn det, og returner den tilbake.
Så, la oss gå tilbake, ALT + Q, for å gå tilbake til Excel. = MIDPART-fanen for det, og det ser ut til at det fungerer. Kopier det ned. Mike, har du en til? (= MIDPart (A2))
Mike: Vel, jeg har en annen, men det kommer til å være en lang formel - ikke så kort som den UDF. Ok, la oss gå over til neste ark. Nå, hvis vi skal lage en formel og vi har litt tekst og det alltid er et annet antall avgrensere, må jeg på en eller annen måte få posisjonen til den siste skilletegnet.
Dette tar noen skritt, men jeg begynner med SUBSTITUTE-funksjonen. Jeg skal se gjennom den teksten, den gamle teksten jeg vil finne er i ”, det -, og hva vil jeg sette i stedet for eller erstatte det? “”. Det vil ikke gi noe. Nå, hvis jeg) og CONTROL + ENTER, hva skal det gjøre? (= SUBSTITUT (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.
Vel, der går du. Jeg vil takke alle for at de var innom. Vi sees neste gang for en annen Dueling Excel-podcast fra og ExcelIsFun.
Last ned fil
Last ned eksempelfilen her: Duel185.xlsm
