Heute ist eine verrückte Frage. Sie haben eine Spalte mit Teilenummern. Die Teilenummer enthält 4 bis 7 Striche. Sie möchten nur den Teil der Teilenummer nach dem ersten Bindestrich und bis zum letzten Bindestrich extrahieren. Dies ist eine duellierende Excel-Episode.
Schau Video
- Ziel ist es, den ersten und letzten Strich zu finden und alles dazwischen zu halten
- Der schwierige Teil hier ist, den letzten Strich zu finden
- Rechnungsmethode 1: Flash Fill
- Füllen Sie die ersten paar manuell aus (einschließlich einiger mit unterschiedlicher Anzahl von Strichen).
- Wählen Sie die leere Zelle darunter aus
- Strg + E auf Flash Fill
- Mike Methode 2:
- Verwenden Sie Power Query
- In Excel 2016 befindet sich Power Query in Excel 2016 in der Gruppe Get & Transform
- Laden Sie in Excel 2010 und 2013 Power Query von Microsoft herunter. Es wird eine neue Registerkarte "Power Query" in der Multifunktionsleiste erstellt
- Konvertieren Sie Ihre Daten mit Strg + T in eine Tabelle
- Verwenden Sie "Daten teilen" in Power Query - zuerst, um am linken Strich zu teilen, dann, um am rechten Strich zu teilen
- Rechnungsmethode 3:
- VBA-Funktion, die vom Ende der Zelle nach hinten iteriert, um den letzten Bindestrich zu finden
- Mike Methode 4:
- Verwenden Sie SUBSTITUTE, um die Position des N-ten Strichs zu ermitteln
- SUBSTITUTE ist die einzige Textfunktion, mit der Sie eine Instanznummer angeben können
- Verwenden Sie, um die Instanznummer zu ermitteln
=LEN(A2)-LEN(SUBSTITUTE)
Video-Transkript
Bill: Hey. Willkommen zurück. Es ist Zeit für einen weiteren Duell-Excel-Podcast. Ich bin Bill Jelen von MrExcel. (Ich werde von Mike Girvin von ExcelIsFun begleitet. Dies ist unsere - 00:03) Folge 185: Auszug aus der ersten - bis zur letzten -.
In Ordung. Die heutige Frage wird von Anvar auf YouTube gesendet. Wie kann ich alles vom ersten bis zum letzten extrahieren und diese Daten überprüfen, die er hier hat? Es gibt eine große Anzahl von Strichen, irgendwo zwischen 3, 5, 6, 7 Strichen, okay?
Mein erster Gedanke ist also, hey, es ist wirklich einfach, den ersten zu finden - oder? = left or = MID of the FIND von A2 und dann das -, +1 in Ordnung, aber um zum letzten zu gelangen - wird mir der Kopf weh tun, richtig, denn wie viele Striche haben wir? Wir könnten das ERSATZ von A2 nehmen, die Bindestriche ersetzen und die Länge davon, die ursprüngliche Länge, vergleichen. Das sagt mir die Anzahl der Striche, aber jetzt weiß ich, welche - zu finden, die 2., 3., 4., 5., aber benutze ich FIND?
Ich war bereit, zu VBA zu gehen, richtig? Das ist meine Knie-Ruck-Reaktion. Ich sagte, warte eine Sekunde. Ich sagte, Anvar, in welcher Excel-Version bist du? Er sagt, ich bin in Excel 2016. Ich sagte, das ist wunderschön. Wenn Sie in Excel 2013 oder neuer arbeiten, können wir diese großartige neue Funktion namens Flash Fill verwenden. Mit Flash Fill müssen wir ihm nur ein Muster geben, und ich werde ihm genug Muster geben, damit ich nicht nur eins mit zwei Strichen nehme und das ein paar Mal mache. Ich möchte sicherstellen, dass ich auf diese Weise ein paar verschiedene Striche habe. Chad im Excel-Team weiß, wonach ich suche. Chad ist der Typ, der die Logik für Flash Fill geschrieben hat. Ich bekomme also ungefähr 3 davon und dann ist CONTROL + E die Abkürzung für die Verwendung von DATA und dann FLASH FILL, und es sieht sicher so aus, als hätte es das Richtige getan. Okay, Mike.Mal sehen, was du hast.
Mike: Danke, MrExcel. Ja. Flash Fill gewinnt. Diese Funktion, Flash Fill, ist eines der modernen Excel-Tools, das einfach unglaublich ist. Wenn es ein einmaliger Deal ist und Sie ein konsistentes Muster haben, hey, so würde ich es machen.
Hey, lass uns zum nächsten Blatt gehen. Anstatt Flash Fill zu verwenden, können wir jetzt auch Power Power verwenden. Jetzt verwende ich Excel 2016, also habe ich die Gruppe GET & TRANSFORM. Das ist Power Query. In früheren Versionen 2013 (bis 10 - 2:30 Uhr) müssen Sie das kostenlose Power Query-Add-In herunterladen.
Damit die Stromabfrage funktioniert, muss sie in eine Excel-Tabelle konvertiert werden. Jetzt würde ich wieder Flash Fill verwenden, wenn dies ein einmaliger Deal wäre. Wann würden Sie Power Query verwenden? Wenn Sie wirklich große Datenmengen hatten oder von einer externen Quelle stammen, ist dies der richtige Weg, oder Sie mögen dies sogar besser, als 3 oder 4 Beispiele für die Flash-Füllung eingeben zu müssen, da wir dies mit der Leistungsabfrage tun können Sagen Sie ausdrücklich, finden Sie den ersten - und finden Sie den letzten -.
Jetzt werde ich dies in eine Excel-Tabelle konvertieren. Ich habe eine einzelne Zelle ausgewählt, leere Zellen ringsum. Ich gehe zu INSERT, TABLE, oder Sie verwenden die Tastatur, CONTROL + T. Ich kann auf OK oder ENTER klicken. Ich möchte diese Tabelle benennen, also gehe ich zu TABLE TOOLS, DESIGN, zu PROPERTIES. Ich werde das STARTKEYTABLE nennen und ENTER. Jetzt kann ich zu DATA zurückkehren und es mit der Schaltfläche FROM TABLE in die Power-Abfrage bringen. Da ist meine Kolumne. Da ist der Name. Ich möchte diesen Namen nicht behalten, da die Ausgabe nach Excel exportiert wird und ich ihm einen anderen Namen geben möchte. Also werde ich es CLEANEDKEYTABLE nennen. Ich brauche diesen GEÄNDERTEN TYP nicht. Ich schaue nur auf die Quelle. Jetzt kann ich auf die Spalte klicken und ganz oben in HOME befindet sich die Schaltfläche SPLIT. Ich kann SPLIT BY DELIMITER sagen. Sieht so aus, als hätte es schon geraten. ICH'Ich werde links sagen. OK klicken.
Wenn ich jetzt hierher schaue, sehe ich GEÄNDERTEN TYP. Ich brauche das nicht, also werde ich diesen Schritt loswerden. Ich habe nur SPLIT COLUMN BY DELIMITER. Jetzt mache ich das noch einmal, aber anstatt die SPLIT-Schaltfläche hier oben zu verwenden, klicken Sie mit der rechten Maustaste auf SPLIT COLUMN, BY DELIMITER, und sehen Sie sich das an. Wir können wählen, ob wir es durch den RECHTSMITTEL teilen möchten. OK klicken. Jetzt brauche ich diese beiden Spalten nicht mehr, also klicke ich mit der rechten Maustaste auf die Spalte, die ich behalten möchte. ENTFERNEN SIE ANDERE SPALTEN. Ich werde diesen geänderten Typ tatsächlich herausbringen. Es wird heißen, sind Sie sicher, dass Sie dies löschen möchten? Ich werde sagen, ja, LÖSCHEN. Da sind meine sauberen Daten.
Jetzt kann ich zu CLOSE & LOAD kommen. SCHLIESSEN & LADEN ZU. Dies ist das neue Dialogfeld IMPORT. Früher hieß LOAD TO, aber ich möchte es in eine Tabelle auf einem vorhandenen Arbeitsblatt laden. Klicken Sie auf die Schaltfläche zum Reduzieren. Ich werde C1 auswählen, den Kollaps aufheben, auf OK klicken und los geht's. Power Query, um unsere Daten zu bereinigen und genau die Daten zu erhalten, die wir wollen. In Ordung. Ich werde es zurückwerfen.
Bill: Da ist genau der Punkt, RIGHT-MOST DELIMITER in der SPLIT COLUMN BY DELIMITER, einer der coolen Funktionen in der Leistungsabfrage. Das ist großartig.
In Ordung. Meine Knie-Ruck-Reaktion - VBA UDF (unverständlich - 05:34) ist wirklich einfach, VBA zu machen. Wechseln Sie zu ALT + F11. EIN MODUL EINFÜGEN. Geben Sie in diesem Modul diesen Code ein. Ich werde eine brandneue Funktion erstellen (erstellen - 05:43), ich werde sie MIDPART nennen, und ich werde einen Text übergeben, und dann werde ich es tun Gehen Sie vom letzten Zeichen in dieser Zelle von der Länge von MYTEXT zurück zu 1, SCHRITT -1 und sehen Sie sich dieses Zeichen an. Die MID von MYTEXT, diese Variable i, sagt uns also, welches Zeichen wir für eine Länge von 1 suchen. Ist es ein -? Sobald ich ein - finde, werde ich die LINKE von MYTEXT ab Zeichen i - 1 nehmen, damit ich alles für das letzte Mal los werde - bis zum Ende und dann sicherstellen, dass ich nicht gehe Suche weiter nach weiteren Strichen, der EXIT FOR bringt mich aus dieser (unverständlichen - 06:17) Schleife heraus.und von dort ist der einfache Teil. Wir nehmen einfach den MYTEXT, beginnen am MID von MYTEXT (wo ich den - 06:26 verwende), verwenden die Funktion FIND, um den ersten zu finden -, gehen 1 mehr als das und geben diesen zurück.
So, let's go back, ALT+Q, to return to Excel. = MIDPART tab of that, and it looks like it's working. Copy that down. Mike, do you have another one? (=MIDPart(A2))
Mike: Well, I do have another one,, but it's going to be one long formula -- not as short as that UDF. Alright, let's go over to the next sheet. Now, if we're going to do a formula and we have some text and there are always a different number of delimiters, somehow, I need to get the position of that last delimiter.
Now, this is going to take a few steps but I'm going to start with the SUBSTITUTE function. I'm going to look through that text, , the old text I want to find is in ”, that -, , and what do I want to put in its place or substitute? “”. That will put nothing in. Now, if I ) and CONTROL+ENTER, what is that going to do? (=SUBSTITUTE(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.
Na siehst du. Ich möchte allen für ihren Besuch danken. Wir sehen uns beim nächsten Mal für einen weiteren Duell-Excel-Podcast von und ExcelIsFun.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Duel185.xlsm
