So trennen Sie eine Spalte mit Excel-Daten in zwei Spalten. So analysieren Sie Daten in Excel.
Schau Video
- Bills erste Methode mit Text in Spalten (auf der Registerkarte Daten).
- Wählen Sie in Schritt 1 "Begrenzt". Wählen Sie in Schritt 2 ein Leerzeichen aus. Überspringen Sie Schritt 3, indem Sie auf Fertig stellen klicken.
- Der Text wird an jeder Stelle geteilt, sodass alles mit drei Wörtern in 3 Zellen endet. Setzen Sie diese wieder zusammen mit
=TEXTJOIN(" ",True,B2:E2)oder - mit
=B2&" "&C2&" "&D2 - Mikes erste Methode verwendet Power Query. Power Query ist Get & Transform im Jahr 2016 oder ein kostenloser Download für 2010 oder 2013.
- Konvertieren Sie zuerst Ihre Daten mit Strg + T in eine Tabelle. Dann in Power Query aus Tabelle. Geteilte Spalte nach Trennzeichen. Wählen Sie Leerzeichen und dann das am weitesten links stehende Trennzeichen.
- Sie können eine Spalte durch Doppelklick umbenennen!
- Schließen & Laden in … und wählen Sie eine neue Stelle im Arbeitsblatt.
- Bills zweite Methode ist die Verwendung von Flash Fill. Geben Sie neue Überschriften in A, B und C ein. Flash Fill funktioniert nicht, wenn Sie keine Überschriften haben! Geben Sie ein Muster für die ersten beiden Zeilen ein.
- Gehen Sie zur ersten leeren Zelle in B und drücken Sie Strg + E. Wiederholen Sie diesen Vorgang für Spalte C.
- Mikes zweite Methode besteht darin, diese Formeln zu verwenden:
- Verwenden Sie für den ersten Teil
=LEFT(A2,SEARCH(" ",A2)-1) - Verwenden Sie für den zweiten Teil
=SUBSTITUTE(A2,B2&" ","")
Video-Transkript
(Musik)
Bill Jelen: Hey, willkommen zurück, es ist Zeit für einen weiteren Duell-Excel-Podcast. Ich bin Bill Jelen von. Ich werde von Mike Girvin von Excel Is Fun begleitet. Das ist unser
Episode 182: Teilen Sie Daten von einer Zelle auf, um sie in zwei Zellen anzuzeigen.
Okay, die heutige Frage wird von Tom geschickt. Gibt es eine Möglichkeit, die Daten einfach in eine Zelle aufzuteilen, damit die Daten in zwei Zellen angezeigt werden? Zum Beispiel 123 Main Street, er möchte 123 in einer Zelle und Main Street in einer anderen Zelle; oder Howard und Howard und dann End. Ich habe unzählige Stunden damit verbracht, diese Art von Daten zu trennen. Ich würde mich freuen, von Ihrem Unternehmen zu hören, obwohl es viele, viele verschiedene Möglichkeiten gibt, dies zu tun.
Als erstes wähle ich alle Daten aus, Strg + Umschalt + Abwärtspfeil und dann Daten, Text in Spalten. Text zu Spalten in Schritt 1 werden die Daten begrenzt. Es wird durch ein Leerzeichen begrenzt und dann einfach auf Fertig stellen klicken. Das Problem bei dieser Methode ist, dass wenn Sie die 123 Main Street haben, diese in 3 statt in 2 Zellen endet. Oh, Power Query würde das so viel einfacher machen, aber hier sind wir. Okay, also was ich tun werde, ist, dass ich weit rechts von den Daten herauskomme, wo ich weiß, dass jenseits dessen, wo alles aufgebaut ist. Wenn ich in Office 365 bin, verwende ich TEXTJOIN. TEXTJOIN, diese großartige Sache, Trennzeichen ist ein Leerzeichen. Ignoriere leere Zellen True und dann die Zellen, die ich so miteinander verketten möchte, und kopiere einfach alle nach unten, Strg + V. Ich kopiere Strg + C und dann Home, Einfügen,Als Werte einfügen und an dieser Stelle kann ich diese 3 zusätzlichen Spalten löschen.
Ahh, aber niemand hat Office 365, oder? Wenn Sie also nicht über Office 365 verfügen, müssen Sie = dieses & "" und das tun, und wenn es mehr "" und das gibt, und wenn es mehr gibt, fahren Sie fort. In diesem Fall ist es sinnlos, weil in D nichts drüben ist, aber Sie haben die Idee. Strg + C, kopiere es in die letzte Datenzeile, Strg + V und dann Strg + C, Alt + ESV, um diese B-Werte zu erhalten. Und da sind wir, okay. Mike, mal sehen, was du hast.
Mike Girvin: Danke. Hey, du hast mich hier leicht gemacht, weil du bereits Get & Transform Power Query erwähnt hast. Der alte Text in Spalten erlaubt dir nur, bei jedem Zeichen ein Leerzeichen zu setzen, oder? Wenn wir Power Query verwenden, können wir dieses Trennzeichen verwenden und sagen: "Hey, teilen Sie es einfach beim ersten Auftreten."
Um diese Daten in den Abfrage-Editor zu übertragen, müssen wir sie in eine Excel-Tabelle konvertieren. Also gehe ich zu Einfügen, Tabelle oder verwende Strg + T. Meine Tabelle hat Überschriften. Die Schaltfläche OK ist hervorgehoben, damit ich mit der Maus darauf klicken oder einfach die Eingabetaste drücken kann. Jetzt möchte ich diese Tabelle benennen, damit ich hierher komme, OriginalData und Enter. Nun, dies ist eine Excel-Tabelle, wir können zu Daten kommen und dort ist es From Table. Das bringt es aus Excel in den Editor. Die Spalte ist ausgewählt: Registerkarte "Startband", wir können "Spalte nach Trennzeichen teilen" sagen oder hierher kommen und mit der rechten Maustaste auf "Spalte nach Trennzeichen teilen" klicken. In der Dropdown-Liste können wir sagen, hey, verwenden Sie ein Leerzeichen und sehen Sie sich das am äußersten linken Trennzeichen an. Wenn ich auf OK klicke, BOOM! Da ist es. Jetzt werde ich beide Spalten benennen: Doppelklicken Sie auf Teil 1 Eingabe, doppelklicken Sie auf Teil 2 und Eingabe. Jetzt,Ich kann hierher kommen oder Close & Load, Close & Load To und ich kann wählen, wo dies platziert werden soll. Ich möchte es definitiv als Tabelle, neues Arbeitsblatt, vorhandenes Arbeitsblatt ausgeben. Markieren Sie dies und klicken Sie auf die Schaltfläche zum Reduzieren. Ich werde D1 sagen, auf OK klicken und dann auf Laden klicken. Und los geht's, unser Power Query Output.
Okay, wirf zurück zu.
Bill Jelen: Oh, Mike, Power Query ist großartig! Ja, das ist ein guter Weg. Hier ist eine andere, die möglicherweise funktioniert, wenn Sie Excel 2013 oder neuer haben.
Und was wir tun werden, ist hier rauszukommen und den ersten Teil und dann den zweiten Teil zu sagen. Stellen Sie sicher, dass Sie diese Überschriften so einfügen, dass sie nicht so sein müssen, wenn Sie diese Überschriften nicht einfügen, aber sie müssen Überschriften haben, sonst funktioniert es nicht. Ich werde 123 und Main Street setzen und dann werden wir Howard und End so setzen. Jetzt, da wir dort ein schönes kleines Muster haben, kommen Sie hier auf der Registerkarte Daten und in der Flash-Füllung heraus, die Strg + E ist. Drücken Sie genau dort Strg + E und dann genau dort Strg + E. Das Schöne ist, wir müssen Daten nicht wie in meinem Beispiel miteinander verketten. Okay, Mike, zurück zu dir.
Mike Girvin: Ding-Ding-Ding. Das ist ohne Zweifel der Gewinner. Flash Fill ist der Weg dorthin. Beachten Sie, dass wir es nicht in eine Tabelle konvertieren oder ein Dialogfeld öffnen mussten. habe nur ein paar Beispiele eingegeben und dann Strg + E.
Okay, wir könnten es mit Formeln machen, obwohl Flash Fill wahrscheinlich schneller wäre. Schauen Sie sich das an, das Muster, genau wie diese Listenzelle, die in Flash Fill verwendet wird, ist alles vor dem ersten Leerzeichen und dann alles danach. Also hey, ich werde die LEFT-Funktion verwenden, der Text ist genau dort und wie viele Zeichen von links? Nun, ich werde nach diesem Leerzeichen suchen - 1 2 3 4 mit der SEARCH-Funktion, Text suchen, Leerzeichen und "" darin. Beachten Sie nun, dass die Suche an den Fingern 1 2 3 4 zählt und zu dem gewünschten Bereich gelangt, also zu I -1) Strg + Eingabetaste, doppelklicken Sie und senden Sie sie nach unten. Das bekommt also immer alles vor dem ersten Leerzeichen.
Beachten Sie jetzt, dass wir hier bereits den Text haben, damit ich die SUBSTITUTE-Funktion verwenden kann. Der Text, den ich durchsehen werde, sind die vollständigen Daten, das Komma, der alte Text, nach dem ich suchen möchte, und dann SUBSTITUT. Nichts ist fast 1 2 3. Ich möchte das Leerzeichen, das ich gerade in der vorherigen Formel herausgenommen habe, wieder hinzufügen. Jetzt wird nach 1 2 3, Leerzeichen und dann nach Howard, Leerzeichen usw., Komma und dann gesucht den neuen Text, den ich ersetzen möchte. Nun, um SUBSTITUTE zu sagen, dass Sie ihn durch nichts ersetzen möchten, sagen Sie "" kein Leerzeichen dazwischen, schließen Sie die Klammer und das wird funktionieren. Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten. In Ordung? Wirf es einfach zurück zu.
Bill Jelen: Hey! Alright, Mike, both of your methods were awesome. Let's do a quick wrap-up here. My first method using Text to Columns: Step 1, choose Delimited; Step 2, choose a space, and then click Finish. The problem is that if you have multiple spaces it’s going to end up in multiple cells. I have to put those back together. Office 365 TEXTJOIN or the old B2&“ ”&C2 and so on.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking. I've been right-clicking and renaming all this time and being a little annoyed of that. That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer. Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing. That was awesome.
Okay, 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 Excel macht Spaß.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Duel182.xlsm
