Vandaag is een gekke vraag. U heeft een kolom met onderdeelnummers. Het onderdeelnummer bevat 4 tot 7 streepjes. U wilt alleen het gedeelte van het onderdeelnummer extraheren na het eerste streepje en tot en met het laatste streepje. Dit is een duellerende Excel-aflevering.
Bekijk video
- Het doel is om het eerste en laatste streepje te vinden en alles ertussenin te houden
- Het moeilijkste hier is het vinden van het laatste streepje
- Factuurmethode 1: snel vullen
- Vul de eerste paar handmatig in (inclusief enkele met een verschillend aantal streepjes)
- Selecteer de lege cel daaronder
- Ctrl + E om snel te vullen
- Mike Methode 2:
- Gebruik Power Query
- In Excel 2016 bevindt Power Query zich in de groep Ophalen en transformeren in Excel 2016
- Download in Excel 2010 en 2013 Power Query van Microsoft. Het maakt een nieuw Power Query-tabblad in het lint
- Converteer uw gegevens naar een tabel met Ctrl + T
- Gebruik Gegevens splitsen in Power Query - eerst om te splitsen bij het meest linkse streepje en vervolgens om te splitsen bij het meest rechtse streepje
- Factuurmethode 3:
- VBA-functie die van het einde van de cel naar achteren herhaalt om het laatste streepje te vinden
- Mike Methode 4:
- Gebruik SUBSTITUTE om de locatie van het N-de streepje te vinden
- SUBSTITUTE is de enige tekstfunctie waarmee u een instantienummer kunt specificeren
- Gebruik om het instantienummer te vinden
=LEN(A2)-LEN(SUBSTITUTE)
Videotranscriptie
Bill: Hé. Welkom terug. Het is tijd voor nog een Dueling Excel-podcast. Ik ben Bill Jelen van MrExcel. (Ik krijg gezelschap van Mike Girvin van ExcelIsFun. Dit is onze - 00:03) aflevering 185: uittreksel van de eerste - tot de laatste -.
Oké. De vraag van vandaag wordt door Anvar op YouTube gestuurd. Hoe kan ik alles van de eerste - tot de laatste - extraheren en deze gegevens bekijken die hij hier heeft. Er is een enorm aantal streepjes, van 3, 5, 6, 7 streepjes, oké?
Dus mijn eerste gedachte is, nou ja, het is heel gemakkelijk om de eerste te vinden - toch? = links of = MIDDEN van de FIND van A2 en dan de -, +1 oké, maar om bij de laatste - te komen, dat zal mijn hoofd pijn doen, juist, want, nou ja, hoeveel streepjes hebben we? We zouden de SUBSTITUUT van A2 kunnen nemen, de streepjes vervangen, en de lengte daarvan kunnen vergelijken, de oorspronkelijke lengte. Dat vertelt me het aantal streepjes, maar nu weet ik welke - ik moet de 2e, 3e, 4e, 5e vinden, maar gebruik ik FIND?
Ik was klaar om naar VBA te gaan, toch? Dat is mijn schokkerige reactie. Ik zei, wacht even. Ik zei: Anvar, in welke versie van Excel zit je? Hij zegt, ik zit in Excel 2016. Ik zei, dat is prachtig. Als u zich in Excel 2013 of nieuwer bevindt, kunnen we deze geweldige nieuwe functie genaamd flash fill gebruiken. Met flitsvulling moeten we het gewoon een patroon geven, en ik ga het genoeg patroon geven, dus het is niet alleen dat ik er een neem met twee streepjes en dat een paar keer doe. Ik wil er zeker van zijn dat ik op die manier een paar verschillende streepjes heb. Chad van het Excel-team weet wat ik zoek. Chad is de man die de logica voor flash-fill heeft geschreven. Dus ik krijg er ongeveer 3 van en dan is CONTROL + E de snelkoppeling voor het gebruik van DATA en vervolgens FLASH FILL, en het ziet er inderdaad naar uit dat het het juiste deed. Oké, Mike.Eens kijken wat je hebt.
Mike: Bedankt, meneer Excel. Ja. Flash-fill wint. Die functie daar, flash-fill, is een van de moderne Excel-tools die gewoon geweldig is. Als het een eenmalige deal is en je hebt een consistent patroon, hé, zo zou ik het doen.
Hé, laten we naar het volgende blad gaan. In plaats van flash-fill te gebruiken, kunnen we nu power-query gebruiken. Nu gebruik ik Excel 2016, dus ik heb de GET & TRANSFORM-groep. Dat is een machtsvraag. In eerdere versies, 2013 (tot 10 - 2:30), moet u eigenlijk de gratis invoegtoepassing Power Query downloaden.
Om Power Query te laten werken, moet dit nu worden geconverteerd naar een Excel-tabel. Nu, nogmaals, ik zou flash-fill gebruiken als dit een eenmalige deal was. Wanneer zou u Power Query gebruiken? Als je echt big data had of van een externe bron kwam, zou dit de juiste keuze zijn, of misschien vind je dit zelfs leuker dan 3 of 4 voorbeelden te moeten typen voor flash-fill, want met power query kunnen we zeg specifiek vind de eerste - en vind de laatste -.
Nu ga ik dit converteren naar een Excel-tabel. Ik heb een enkele cel geselecteerd, lege cellen helemaal rond. Ik ga naar INSERT, TABLE, of je gebruikt het toetsenbord, CONTROL + T. Ik kan op OK of ENTER klikken. Ik wil deze tafel een naam geven, dus ik ga naar TABLE TOOLS, ONTWERP, naar EIGENSCHAPPEN. Ik ga dit STARTKEYTABLE noemen en ENTER. Nu kan ik teruggaan naar DATA, het in power-query brengen met behulp van de FROM TABLE-knop. Daar is mijn column. Daar is de naam. Ik wil deze naam niet behouden omdat de uitvoer naar Excel wordt geëxporteerd en ik het een andere naam wil geven. Dus ik noem het CLEANEDKEYTABLE. Ik heb dat GEWIJZIGDE TYPE niet nodig. Ik kijk alleen naar de bron. Nu kan ik op de kolom klikken en, helemaal boven in HOME, is er de SPLIT-knop. Ik kan zeggen SPLIT, DOOR DELIMITER. Het lijkt erop dat het al geraden is. IK'Ik ga LINKS-MEEST zeggen. Klik OK.
Als ik hier nu kijk, zie ik VERANDERD TYPE. Ik heb dat niet nodig, dus ik ga van die stap af. Ik heb alleen SPLIT COLUMN BY DELIMITER. Nu ga ik dit opnieuw doen, maar in plaats van de SPLIT-knop hierboven te gebruiken, klik met de rechtermuisknop omlaag naar SPLIT COLUMN, BY DELIMITER, en kijk daarnaar. We kunnen ervoor kiezen om het te splitsen met de RECHTER-MEESTE DELIMITER. Klik OK. Nu heb ik deze twee kolommen niet nodig, dus ik ga met de rechtermuisknop op de kolom klikken die ik wil behouden, ANDERE KOLOMMEN VERWIJDEREN. Ik ga dit GEWIJZIGDE TYPE eigenlijk X uit. Het gaat zeggen BENT U ZEKER DAT U DIT WILT VERWIJDEREN? Ik ga zeggen, ja, VERWIJDEREN. Er zijn mijn schone gegevens.
Nu kan ik naar CLOSE & LOAD komen. DICHT EN LADEN NAAR. Dit is het nieuwe IMPORT-dialoogvenster. Vroeger stond er LADEN AAN, maar ik wil het naar een tafel laden, op een BESTAAND WERKBLAD. Klik op de knop voor samenvouwen. Ik ga C1 selecteren, uitklappen, op OK klikken en daar gaan we. Krachtige query om onze gegevens op te schonen en alleen de gegevens te krijgen die we willen. Oké. Ik gooi het terug naar.
Bill: Daar is het punt precies, RECHTS-MEESTE DELIMITER in de SPLIT COLUMN BY DELIMITER, een van de coole functies in Power Query. Dat is geweldig.
Oké. Mijn schokkende reactie - VBA UDF (onverstaanbaar - 05:34) heel gemakkelijk om VBA te doen. Schakel over naar ALT + F11. PLAATS EEN MODULE. Typ deze code in die module. Ik ga (maak een - 05:43) gloednieuwe functie, ik ga het MIDPART noemen, en ik ga er wat tekst aan doorgeven, en dan wat ik ga doen is: ga van het laatste karakter in die cel van de lengte van MYTEXT terug naar 1, STAP -1 en kijk naar dat karakter. Dus het MIDDEN van MYTEXT, die variabele i, vertelt ons naar welk teken we kijken voor lengte van 1. Is het een -? Zodra ik een - vind, ga ik LINKS van MYTEXT beginnend bij karakter i - 1, dus ik doe alles weg voor die laatste - helemaal naar buiten, en zorg er dan voor dat ik niet ga blijf zoeken naar meer streepjes, de EXIT FOR haalt me uit deze (onverstaanbare - 06:17) lus,en van daaruit is het makkelijke gedeelte. We gaan gewoon de MYTEXT nemen, beginnen bij het MIDDEN van MYTEXT, (waar ik de - 06:26 gebruik) de functie FIND gebruiken om de eerste te vinden -, 1 meer dan dat, en dat teruggeven.
Dus laten we teruggaan, ALT + Q, om terug te keren naar Excel. = MIDPART-tabblad daarvan, en het lijkt erop dat het werkt. Kopieer dat naar beneden. Mike, heb je er nog een? (= MIDPart (A2))
Mike: Nou, ik heb er nog een, maar het wordt een lange formule - niet zo kort als die UDF. Oké, laten we naar het volgende blad gaan. Als we nu een formule gaan maken en we hebben wat tekst en er is altijd een ander aantal scheidingstekens, dan moet ik op de een of andere manier de positie van dat laatste scheidingsteken krijgen.
Nu gaat dit een paar stappen kosten, maar ik ga beginnen met de functie SUBSTITUTE. Ik ga die tekst doorzoeken, de oude tekst die ik wil vinden, staat in ”, dat …, en wat wil ik in de plaats zetten of vervangen? "". Dat levert niets op. Als ik) en CONTROL + ENTER, wat gaat dat doen? (= VERVANGEN (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.
Nou, daar ga je. Ik wil iedereen bedanken voor het langskomen. We zien je de volgende keer voor nog een Dueling Excel-podcast van en ExcelIsFun.
Download bestand
Download het voorbeeldbestand hier: Duel185.xlsm
