So teilen Sie Zahlen und Text aus einer Zeichenfolge in Excel

Oft erhalte ich gemischte Daten von Feld und Server zur Analyse. Diese Daten sind normalerweise schmutzig, da Spalten mit Zahlen und Text vermischt sind. Bei der Datenbereinigung vor der Analyse trenne ich Zahlen und Text in separaten Spalten. In diesem Artikel erkläre ich dir, wie du das machen kannst.

Szenario:
Einer unserer Freunde auf Exceltip.com stellte diese Frage im Kommentarbereich. „Wie trenne ich Zahlen vor einem Text und am Ende des Textes mit Excel-Formel? Zum Beispiel 125EvenueStreet und LoveYou3000 usw.“

Zum Extrahieren von Text verwenden wir RIGHT, LEFT, MID und andere Textfunktionen. Wir müssen nur die Anzahl der zu extrahierenden Texte kennen. Und hier werden wir das gleiche zuerst tun.
Extrahieren von Zahl und Text aus einer Zeichenfolge, wenn Zahl am Ende der Zeichenfolge steht
Für obiges Beispiel habe ich dieses Blatt vorbereitet. In Zelle A2 habe ich die Zeichenfolge. In Zelle B2 möchte ich den Textteil und in C2 den Zahlenteil.

Wir müssen also nur die Position kennen, von der aus die Zahl beginnt. Dann verwenden wir Left und andere Funktionen. Um die Position der ersten Zahl zu erhalten, verwenden wir die folgende generische Formel:
Generische Formel zum Abrufen der Position der ersten Zahl im String:

=MIN(SUCHE({0,1,2,3,4,5,6,7,8,9},String_Ref&"0123456789")

Dies gibt die Position der ersten Zahl zurück.
Für das obige Beispiel schreiben Sie diese Formel in eine beliebige Zelle.

=MIN(SUCHE({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))

Textteil extrahieren

Es wird 15 zurückgegeben, da die erste gefundene Zahl an der 15. Position im Text steht. Ich werde es später erklären.

Um nun Text zu erhalten, müssen wir von links nur 15-1 Zeichen aus der Zeichenfolge abrufen. Also werden wir verwenden
LEFT-Funktion zum Extrahieren von Text.
Formel zum Extrahieren von Text von links

=LINKS(A5,MIN(SUCHE({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)

Hier haben wir einfach 1 von jeder Zahl abgezogen, die von MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")) zurückgegeben wird.
Nummernteil extrahieren

Um nun Zahlen zu erhalten, müssen wir nur Zahlenzeichen aus der ersten gefundenen Zahl abrufen. Wir berechnen also die Gesamtlänge von Schnur und subtrahiere die Position der ersten gefundenen Zahl und addiere 1 dazu. Einfach. Ja, es klingt einfach komplex, es ist einfach.
Formel zum Extrahieren von Zahlen von rechts

=RECHTS(A5,LEN(A5)-MIN(SUCHE({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))+1)

Hier haben wir nur die Gesamtlänge der Zeichenfolge mit der LEN-Funktion erhalten und dann die Position der ersten gefundenen Zahl abgezogen und dann 1 hinzugefügt. Dies gibt uns die Gesamtzahl der Zahlen. Erfahren Sie hier mehr über das Extrahieren von Text mit den LINKS- und RECHTS-Funktionen von Excel.

Der Funktionsteil LINKS und RECHTS ist also einfach. Der schwierige Teil ist der MIN- und der SEARCH-Teil, der uns die Position der ersten gefundenen Zahl angibt. Lass uns das verstehen.
Wie es funktioniert
Wir wissen, wie LINKS und RECHTS funktionieren. Wir werden den Hauptteil dieser Formel untersuchen, der die Position der ersten gefundenen Zahl ermittelt, und zwar: MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String&"0123456789 ")
Die Funktion SEARCH gibt die Position eines Textes in einer Zeichenfolge zurück. Die Funktion SEARCH(‘text’,’string’) nimmt zwei Argumente an, erstens den Text, den Sie durchsuchen möchten, zweitens den String, in dem Sie suchen möchten.

- Hier in SEARCH haben wir an der Textposition ein Array von Zahlen von 0 bis 9. Und an der Zeichenfolgenposition haben wir eine Zeichenfolge, die mit "0123456789" verkettet ist, indem & Operator. Wieso den? Ich werde Ihnen sagen.
- Jedes Element im Array {0,1,2,3,4,5,6,7,8,9} wird im angegebenen String gesucht und gibt seine Position im Array-Form-String am gleichen Index im Array zurück.
- Wenn ein Wert nicht gefunden wird, wird ein Fehler verursacht. Daher führen alle Formeln zu einem Fehler. Um dies zu vermeiden, haben wir die Zahlen "0123456789" im Text verkettet. Damit es immer jede Zahl im String findet. Diese Zahlen sind am Ende, daher wird es kein Problem geben.
- Jetzt gibt die MIN-Funktion den kleinsten Wert aus dem von der SEARCH-Funktion zurückgegebenen Array zurück. Dieser kleinste Wert ist die erste Zahl in der Zeichenfolge. Mit dieser NUMBER- und LEFT- und RIGHT-Funktion können wir nun die Text- und Stringteile aufteilen.

Betrachten wir unser Beispiel. In A5 haben wir die Zeichenfolge mit Straßenname und Hausnummer. Wir müssen sie in verschiedene Zellen trennen.
Sehen wir uns zuerst an, wie wir unsere Position der ersten Zahl in der Zeichenfolge erhalten haben.

- MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")): Dies wird übersetzt in MIN(SEARCH({0,1,2,3, 4,5,6,7,8,9}“,Monta270123456789”))

Nun, wie ich erklärt habe, sucht die Suche jede Zahl im Array {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 und gibt seine Position in einem Array-Formular zurück. Das zurückgegebene Array ist {8,9,6,11,12,13,14,7,16,17}. Wie?
0 wird im String gesucht. Es befindet sich an der 8. Position. Daher ist unser erstes Element 8. Beachten Sie, dass unser Originaltext nur 7 Zeichen lang ist. Kapiert. 0 ist kein Teil von Monta27.
Die nächste 1 wird im String gesucht und ist auch nicht Teil des ursprünglichen Strings, und wir erhalten seine Position 9.
Als nächstes werden 2 gesucht. Da es sich um einen Teil des ursprünglichen Strings handelt, erhalten wir seinen Index als 6.
In ähnlicher Weise wird jedes Element an einer bestimmten Position gefunden.

- Jetzt wird dieses Array als MIN({8,9,6,11,12,13,14,7,16,17}) an die MIN-Funktion übergeben. MIN gibt die 6 zurück, die Position der ersten im Originaltext gefundenen Zahl.
  Und die Geschichte danach ist ganz einfach. Wir verwenden diese Zahl, um Text und Zahlen mit der LEFT- und RIGHT-Funktion zu extrahieren.

Extrahieren von Zahl und Text aus einer Zeichenfolge, wenn Zahl am Anfang der Zeichenfolge steht
Im obigen Beispiel befand sich Number am Ende der Zeichenfolge. Wie extrahieren wir Zahlen und Text, wenn die Zahl am Anfang steht?

Ich habe eine ähnliche Tabelle wie oben vorbereitet. Es hat nur Nummer am Anfang.

Hier verwenden wir eine andere Technik. Wir zählen die Länge der Zahlen (hier 2) und extrahieren diese Anzahl von Zeichen links von String.
Die Methode ist also =LEFT (String, Anzahl der Zahlen)
Um die Anzahl der Zeichen zu zählen, ist dies die Formel.
Generische Formel zum Zählen der Anzahl von Zahlen:

=SUM(LEN(string)-LEN(SUBSTITUTE(string,{"0","1","2","3","4","5","6","7","8" ,"9"},""))

Hier,

- - Die Funktion ERSATZ ersetzt jede gefundene Zahl durch „“ (leer). Wenn eine Zahl als ersetzt gefunden wird und eine neue Zeichenfolge zum Array hinzugefügt wird, wird andernfalls die ursprüngliche Zeichenfolge zum Array hinzugefügt. Auf diese Weise haben wir ein Array von 10 Zeichenfolgen.
  - Jetzt gibt die LEN-Funktion die Länge der Zeichen in einem Array dieser Strings zurück.
  - Dann subtrahieren wir von der Länge der ursprünglichen Strings die Länge jedes Strings, der von der SUBSTITUTE-Funktion zurückgegeben wird. Dies wird wieder ein Array zurückgeben.
  - Jetzt addiert SUM alle diese Zahlen. Dies ist die Anzahl der Zahlen in einer Zeichenfolge.

Zahlenteil aus String extrahieren

Da wir nun die Länge der Zahlen in kennen Schnur, wir werden diese Funktion in LEFT ersetzen.
Da wir unsere Saite eine A11 haben unsere:

Formel zum Extrahieren von Zahlen von LINKS

=LINKS(A11,SUMME(LEN(A11)-LEN(ERSATZ(A11,{"0","1","2","3","4","5","6","7" ,"8","9"},""))))

Textteil aus String extrahieren

Da wir die Anzahl der Zahlen kennen, können wir sie von der Gesamtlänge der Zeichenfolge subtrahieren, um Zahlenalphabete in der Zeichenfolge zu erhalten, und dann die rechte Funktion verwenden, um diese Anzahl von Zeichen rechts von der Zeichenfolge zu extrahieren.

Formel zum Extrahieren von Text von RECHTS

=RECHTS(A11,LEN(A2)-SUM(LEN(A11)-LEN(ERSETZEN(A11,{"0","1","2","3","4","5","6 ","7","8","9"},""))))

Wie es funktioniert
Der Hauptteil in beiden Formeln ist SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6"," 7","8","9"},""))), die das erste Auftreten einer Zahl berechnet. Erst nachdem wir dies gefunden haben, können wir Text und Nummer mit der LINKS-Funktion aufteilen. Also lass uns das verstehen.

- - ERSATZ(A11,{"0","1","2","3","4","5","6","7","8","9"},""): Dieser Teil gibt ein String-Array in A11 zurück, nachdem diese Zahlen durch nichts/Leerzeichen ("") ersetzt wurden. Zum 27Monta es wird {"27Monta","27Monta","7Monta","27Monta","27Monta","27Monta","27Monta","2Monta","27Monta","27Monta"} zurückgegeben.
  - LEN(ERSATZ(A11,{"0","1","2","3","4","5","6","7","8","9"},"" )): Jetzt wird der SUBSTITUTE-Teil von der LEN-Funktion umschlossen. Diese Rückgabelänge von Texten im Array, die von der Funktion SUBSTITUTE zurückgegeben werden. Als Ergebnis haben wir {7,7,6,7,7,7,7,6,7,7}.
  - LEN(A11)-LEN(ERSETZEN(A11,{"0","1","2","3","4","5","6","7","8","9 "},"")): Hier subtrahieren wir jede vom obigen Teil zurückgegebene Zahl von der Länge des tatsächlichen Strings. Die Länge des Originaltextes beträgt 7. Daher haben wir {7-7,7-7,7-6,… .}. Schließlich haben wir {0,0,1,0,0,0,0,1,0,0}.
  - SUM(LEN(A11)-LEN(ERSATZ(A11,{"0","1","2","3","4","5","6","7","8", "9"},""))): Hier haben wir SUM verwendet, um das vom obigen Teil der Funktion zurückgegebene Array zu summieren. Dies ergibt 2. Das ist die Anzahl der Zahlen in der Zeichenfolge.

Damit können wir nun die Texte und Zahlen extrahieren und in verschiedene Zellen aufteilen. Diese Methode funktioniert sowohl mit Text, wenn die Zahl am Anfang und am Ende steht. Sie müssen nur die LEFT- und RIGHT-Funktion entsprechend verwenden.
Verwenden Sie die Funktion SplitNumText, um Zahlen und Texte aus einer Zeichenfolge aufzuteilen
Die oben genannten Methoden sind etwas komplex und nicht sinnvoll, wenn Text und Zahlen gemischt werden. Um Text und Zahlen aufzuteilen, verwenden Sie diese benutzerdefinierte Funktion.

Syntax:

=SplitNumText(string, op)

Zeichenfolge: Die Zeichenfolge, die Sie teilen möchten.
Betrieb: das ist boolesch. Pass 0 oder falsch Textteil zu bekommen. Für Nummernteil, bestanden wahr oder eine beliebige Zahl größer als 0.
Wenn die Zeichenfolge beispielsweise in A20 ist, dann
Die Formel zum Extrahieren von Zahlen aus einer Zeichenfolge lautet:

=SplitNumText(A20,1)

Und
Die Formel zum Extrahieren von Text aus einer Zeichenfolge lautet:

=SplitNumText(A20,0)

Kopieren Sie den folgenden Code in das VBA-Modul, damit die obige Formel funktioniert.

Funktion SplitNumText(str As String, op As Boolean) num = "" txt = "" For i = 1 To Len(str) If IsNumeric(Mid(str, i, 1)) Then num = num & Mid(str, i , 1) Else txt = txt & Mid(str, i, 1) End If Next i If op = True Then SplitNumText = num Sonst SplitNumText = txt End If End Function

Dieser Code überprüft einfach jedes Zeichen im String, ob es eine Zahl ist oder nicht. Wenn es eine Zahl ist, wird sie in der num-Variable gespeichert, sonst in der txt-Variable. Wenn der Benutzer true für op übergibt, wird num zurückgegeben, sonst wird txt zurückgegeben.

Dies ist meiner Meinung nach der beste Weg, um Zahlen und Text aus einer Zeichenfolge aufzuteilen.
Sie können die Arbeitsmappe hier herunterladen, wenn Sie möchten.

Also ja, Leute, dies sind die Möglichkeiten, Text und Zahlen in verschiedene Zellen aufzuteilen. Lassen Sie es mich wissen, wenn Sie Zweifel oder eine bessere Lösung im Kommentarbereich unten haben. Es macht immer Spaß, mit Jungs zu interagieren.

Klicken Sie auf den folgenden Link, um die Arbeitsdatei herunterzuladen:

Zahlen und Text aus einer Zelle teilen

Populäre Artikel:
50 Excel Shortcuts zur Steigerung Ihrer Produktivität
Die SVERWEIS-Funktion in Excel
ZÄHLENWENN in Excel 2016
So verwenden Sie die SUMIF-Funktion in Excel