Wie Text-String in Excel durch Komma, Leerzeichen, Zeichen oder Maske aufgeteilt
Das Tutorial erklärt, wie Zellen in Excel aufgeteilt Formeln und die Split-Text-Funktion. Sie lernen, wie Sie Text durch Komma, Leerzeichen oder ein anderes Trennzeichen trennen und Zeichenfolgen in Text und Zahlen aufteilen.
Das Aufteilen von Text aus einer Zelle in mehrere Zellen ist die Aufgabe, mit der sich alle Excel-Benutzer ab und zu befassen., In einem unserer früheren Artikel haben wir besprochen, wie Zellen in Excel mithilfe der Text-zu-Spalten-Funktion, des Flash-Fill-und Split-Names-Add-Ins aufgeteilt werden. Heute werden wir uns eingehend damit befassen, wie Sie Zeichenfolgen mithilfe von Formeln und der Funktion „Text teilen“ aufteilen können.,
- So teilen Sie Zellen in Excel mithilfe von Formeln auf
- String nach Komma, Doppelpunkt, Schrägstrich, Bindestrich oder einem anderen Trennzeichen teilen
- String nach Zeilenumbruch teilen
- Formeln zum Teilen von Text und Zahlen
- So trennen Sie Zellen mit der Funktion zum Teilen von Text
- Zelle nach Zeichen teilen
- Zelle nach Zeichenfolge teilen
- Zelle nach Maske/li>
So teilen Sie Text in Excel mit Formeln
Um Zeichenfolgen in Excel aufzuteilen, verwenden Sie im Allgemeinen die Funktion LEFT, RIGHT oder MID in Kombination mit FIND oder SEARCH., Auf den ersten Blick mögen einige der Formeln komplex aussehen, aber die Logik ist in der Tat recht einfach, und die folgenden Beispiele geben Ihnen einige Hinweise.
String durch Komma, Doppelpunkt, Schrägstrich, Bindestrich oder anderes Trennzeichen teilen
Beim Teilen von Zellen in Excel besteht der Schlüssel darin, die Position des Trennzeichens innerhalb der Textzeichenfolge zu lokalisieren. Abhängig von Ihrer Aufgabe kann dies entweder durch Groß-und Kleinschreibung Suche oder Groß-und Kleinschreibung FINDEN erfolgen. Sobald Sie die Position des Trennzeichens haben, verwenden Sie die rechte, linke oder mittlere Funktion, um den entsprechenden Teil der Textzeichenfolge zu extrahieren., Betrachten wir zum besseren Verständnis das folgende Beispiel.
Angenommen, Sie haben eine Liste von SKUs des Item-Color-Size-Musters und möchten die Spalte in 3 separate Spalten aufteilen:
So teilen Sie Zeichenfolge nach Zeilenumbruch in Excel
Um Text nach Leerzeichen aufzuteilen, verwenden Sie Formeln, die denen ähneln, die im vorherigen Beispiel gezeigt wurden. Der einzige Unterschied besteht darin, dass Sie die CHAR-Funktion benötigen, um das Zeilenumbruchzeichen anzugeben, da Sie es nicht direkt in die Formel eingeben können.,
Angenommen, die Zellen, die Sie teilen möchten, sehen ähnlich aus:
Nehmen Sie die Formeln aus dem vorherigen Beispiel und ersetzen Sie einen Bindestrich („-„) durch CHAR(10), wobei 10 der ASCII-Code für den Zeilenvorschub ist.
Und so sieht das Ergebnis aus:
So teilen Sie Text und Zahlen in Excel auf
Zunächst gibt es keine universelle Lösung, die für alle alphanumerischen Zeichenfolgen funktioniert. Welche Formel verwendet werden soll, hängt vom jeweiligen Zeichenfolgenmuster ab. Nachfolgend finden Sie die Formeln für die beiden gängigen Szenarien.,
Split-string „text + Nummer“ pattern
Angenommen, Sie haben eine Spalte mit Zeichenfolgen mit text und zahlen, die kombiniert werden, in denen eine Zahl folgt immer text. Sie möchten die ursprünglichen Zeichenfolgen so aufteilen, dass Text und Zahlen wie folgt in separaten Zellen angezeigt werden:
Das Ergebnis kann auf zwei verschiedene Arten erzielt werden.,
Methode 1: Zählen Sie Ziffern und extrahieren Sie so viele Zeichen
Der einfachste Weg, Textzeichenfolgen zu teilen, bei denen die Zahl nach dem Text steht, ist folgender:
Um Zahlen zu extrahieren, suchen Sie die Zeichenfolge nach jeder möglichen Zahl von 0 bis 9, ermitteln Sie die Gesamtzahl der Zahlen und geben Sie so viele Zeichen vom Ende der Zeichenfolge zurück.,
Mit der ursprünglichen Zeichenfolge in A2 lautet die Formel wie folgt:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
Um Text zu extrahieren, berechnen Sie, wie viele Textzeichen die Zeichenfolge enthält, indem Sie die Anzahl der extrahierten Ziffern (C2) von der Gesamtlänge der ursprünglichen Zeichenfolge in A2 subtrahieren. Danach verwenden Sie die LINKE Funktion, um so viele Zeichen vom Anfang der Zeichenfolge zurückzugeben.,
=LEFT(A2,LEN(A2)-LEN(C2))
Wobei A2 die ursprüngliche Zeichenfolge und C2 die extrahierte Zahl ist, wie im Screenshot gezeigt:
Methode 2: Ermitteln Sie die Position der 1.Ziffer in einer Zeichenfolge
Eine alternative Lösung wäre die Verwendung der folgenden Formel, um die Position der ersten Ziffer in der Zeichenfolge zu bestimmen:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
Die detaillierte Erklärung der Formel“s Logik finden Sie hier.,
Sobald die Position der ersten Ziffer gefunden wurde, können Sie Text und Zahlen mithilfe sehr einfacher LINKER und RECHTER Formeln aufteilen.
Zum Extrahieren von Text:
=LEFT(A2, B2-1)
Zum Extrahieren von Nummer:
=RIGHT(A2, LEN(A2)-B2+1)
Wobei A2 die ursprüngliche Zeichenfolge und B2 die Position der ersten Zahl ist., die erste ziffer, können sie die MIN formel in die LINKS und RECHTS funktionen:
Formel zu extrahieren text:=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
Formel zu extrahieren zahlen:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
Split string von „anzahl + text“ muster
Wenn sie sind splitting zellen wo text erscheint nach anzahl, können sie extrahieren zahlen mit die folgenden formel:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
Die Formel ähnelt der im vorherigen Beispiel beschriebenen, mit der Ausnahme, dass Sie die LINKE Funktion anstelle von RECHTS verwenden, um die Nummer von der linken Seite der Zeichenfolge abzurufen.,
Sobald Sie die Zahlen haben, extrahieren Sie Text, indem Sie die Anzahl der Ziffern von der Gesamtlänge der ursprünglichen Zeichenfolge subtrahieren:
=RIGHT(A2,LEN(A2)-LEN(B2))
Wobei A2 die ursprüngliche Zeichenfolge und B2 die extrahierte Nummer ist, wie im folgenden Screenshot gezeigt:
So können Sie Strings in Excel mit verschiedenen Kombinationen verschiedener Funktionen aufteilen., Wie Sie sehen, sind die Formeln alles andere als offensichtlich, daher möchten Sie möglicherweise die Excel Split Cells-Arbeitsmappe herunterladen, um sie näher zu untersuchen.
Wenn das Herausfinden der arkanen Wendungen von Excel-Formeln nicht Ihre Lieblingsbeschäftigung ist, mögen Sie möglicherweise die visuelle Methode zum Teilen von Zellen in Excel, die im nächsten Teil dieses Tutorials gezeigt wird.,
So teilen Sie Zellen in Excel mit der Split Text-Funktion
Eine alternative Möglichkeit, eine Spalte in Excel aufzuteilen, ist die Verwendung der Split Text-Funktion, die in unserer Ultimate Suite for Excel enthalten ist und die folgenden Optionen bietet:
- Split cell by character
- Split cell by string
- Split cell by mask (pattern)
Um die Dinge klarer zu machen, schauen wir uns jede Option einzeln an.
Zellen nach Zeichen aufteilen
Wählen Sie diese Option, wenn Sie den Zellinhalt bei jedem Auftreten des angegebenen Zeichens aufteilen möchten.,
Nehmen wir für dieses Beispiel die Zeichenfolgen des Item-Color-Size-Musters, das wir im ersten Teil dieses Tutorials verwendet haben. Wie Sie sich vielleicht erinnern, haben wir sie mit 3 verschiedenen Formeln in 3 verschiedene Spalten unterteilt. Und hier ist, wie Sie das gleiche Ergebnis in 2 schnellen Schritten erreichen können:
- Vorausgesetzt, Sie Ultimate Suite installiert haben, wählen Sie die Zellen zu teilen, und klicken Sie auf das Split-Text-Symbol auf der Registerkarte Ablebits Daten.,
- Der geteilte Textbereich wird auf der rechten Seite Ihres Excel-Fensters geöffnet und Sie gehen wie folgt vor:
- Erweitern Sie die Gruppe Nach Zeichen aufgeteilt und wählen Sie eines der vordefinierten Trennzeichen aus oder geben Sie ein anderes Zeichen in das benutzerdefinierte Feld ein.
- Wählen Sie, ob Zellen in Spalten oder Zeilen aufgeteilt werden sollen.
- Überprüfen Sie das Ergebnis im Abschnitt Vorschau und klicken Sie auf die Schaltfläche Teilen.
Fertig! Die Aufgabe, für die 3 Formeln und 5 verschiedene Funktionen erforderlich waren, dauert jetzt nur noch ein paar Sekunden und einen Knopfdruck.
Zellen nach Zeichenfolgen teilen
Mit dieser Option können Sie Zeichenfolgen mit einer beliebigen Kombination von Zeichen als Trennzeichen aufteilen. Technisch gesehen teilen Sie eine Zeichenfolge in Teile auf, indem Sie einen oder mehrere verschiedene Teilzeichenfolgen als Grenzen für jeden Teil verwenden.,
Um beispielsweise einen Satz durch die Konjunktionen „und“ und „oder“ zu teilen, erweitern Sie die Gruppe „Nach Zeichenfolgen teilen“ und geben Sie die Trennzeichenfolgen pro Zeile ein:
Als Ergebnis wird die Quellphrase bei jedem Auftreten jedes Trennzeichens getrennt:
Und hier noch ein reales Beispiel., Angenommen, Sie haben eine Datumsspalte aus einer externen Quelle importiert, die wie folgt aussieht:
5.1.2016 12:20
5.2.2016 14:50
Dieses Format ist für Excel nicht üblich und daher würde keine der Datumsfunktionen eines der Datums-oder Zeitelemente erkennen. Um Tag, Monat, Jahr, Stunden und Minuten in separate Zellen aufzuteilen, geben Sie die folgenden Zeichen in das Feld Nach Zeichenfolgen teilen ein:
- Dot (.,) zum Trennen von Tag, Monat und Jahr
- Doppelpunkt (:) zum Trennen von Stunden und Minuten
- Leerzeichen zum Trennen von Datum und Uhrzeit
Klicken Sie auf die Schaltfläche Teilen, und Sie erhalten sofort das Ergebnis:
Zellen nach Maske (Muster)
Trennen einer Zelle durch Maske bedeutet Aufteilen einer Zeichenfolge basierend auf einem Muster.
Diese Option ist sehr praktisch, wenn Sie eine Liste homogener Zeichenfolgen in einige Elemente oder Teilzeichenfolgen aufteilen müssen., Die Komplikation besteht darin, dass der Quelltext nicht bei jedem Auftreten eines bestimmten Trennzeichens aufgeteilt werden kann, sondern nur bei bestimmten Vorkommen. Das folgende Beispiel erleichtert das Verständnis.
Angenommen, Sie haben eine Liste von Zeichenfolgen aus einer Protokolldatei extrahiert:
Sie möchten Datum und Uhrzeit haben, falls vorhanden, Fehlercode und Ausnahmedetails in 3 separaten Spalten., Sie können kein Leerzeichen als Trennzeichen verwenden, da es Leerzeichen zwischen Datum und Uhrzeit gibt, die in einer Spalte angezeigt werden sollen, und Leerzeichen innerhalb des Ausnahmetextes, die ebenfalls in einer Spalte angezeigt werden sollen.
Die Lösung teilt eine Zeichenfolge durch die folgende Maske auf:*FEHLER:*Ausnahme: *
Wobei das Sternchen ( * ) eine beliebige Anzahl von Zeichen darstellt.
Die Doppelpunkte (:) sind in den Trennzeichen enthalten, weil wir nicht wollen, dass sie in den resultierenden Zellen erscheinen.,
Erweitern Sie nun den Abschnitt Nach Maske teilen im Bereich Geteilter Text, geben Sie die Maske in das Feld Trennzeichen eingeben ein und klicken Sie auf Teilen:
Das Ergebnis sieht ähnlich aus:
Ein großer Vorteil dieser Methode ist die Flexibilität.,wenn Sie möchten, dass sie in verschiedenen Spalten angezeigt werden, verwenden Sie diese Maske:
* *ERROR:*Exception:*
Übersetzt in Klartext weist die Maske das Add-In an, die ursprünglichen Zeichenfolgen in 4 Teile zu unterteilen:
- Alle Zeichen vor dem 1.Leerzeichen in der Zeichenfolge (Datum)
- Zeichen zwischen dem 1. Leerzeichen und dem Wort ERROR: (Zeit)
- Text zwischen ERROR: und Exception: (Fehlercode)
- Alles, was nach der Ausnahme kommt: (Ausnahmetext)
Ich hoffe, Ihnen hat diese schnelle und einfache Möglichkeit gefallen, Zeichenfolgen in Excel aufzuteilen., Wenn Sie neugierig sind, es auszuprobieren, steht unten eine Evaluierungsversion zum Download zur Verfügung. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche auf unserem Blog zu sehen!
downloads
Excel Geteilte Zellen Formeln (.xlsx-Datei)
Ultimate Suite 14-Tage voll funktionsfähige version (.,zip-Datei)
- So teilen Sie Zellen in Excel auf
- So trennen Sie Namen in Excel: Vor-und Nachname in verschiedene Spalten aufteilen
- So teilen Sie Datum und Uhrzeit in Excel auf
- So führen Sie zwei Spalten in Excel zusammen, ohne Daten zu verlieren