Wie Zahlen und Text von String in Excel nach Split

Einer unserer Freunde auf Exceltip.com hat diese Frage im Kommentarbereich gestellt. „Wie trenne ich Zahlen, die vor einem Text und am Ende eines Textes stehen, mithilfe der Excel-Formel? Zum Beispiel 125EvenueStreet und LoveYou3000 usw. ” Zum Extrahieren von Text verwenden wir RECHTS, LINKS, MITTEL und andere Textfunktionen. Wir müssen nur die Anzahl der zu extrahierenden Texte kennen. Und hier werden wir zuerst dasselbe tun.

Nummer und Text aus einer Zeichenfolge extrahieren, wenn sich die Zahl am Ende der Zeichenfolge befindet. Für das obige 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, an der die Nummer beginnt. Dann werden wir Left und andere Funktionen verwenden. Um die Position der ersten Zahl zu erhalten, verwenden wir die folgende generische Formel:

Dies gibt die Position der ersten Nummer zurück.

Für das obige Beispiel schreiben Sie diese Formel in eine beliebige Zelle.

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

Um Text zu erhalten, müssen wir von links nur 15-1 Zeichen aus der Zeichenfolge abrufen. Also werden wir link: / excel-text-extract-text-from-a-string-in-excel-using-excels-left-and-right-function [LEFT-Funktion zum Extrahieren von Text.] |||| verwenden

| === Formel zum Extrahieren von Text von links

Hier haben wir gerade 1 von der von MIN zurückgegebenen Zahl abgezogen (link: / text-excel-search-function [SEARCH] (\ {0,1,2,3,4,5,6,7,8,9) }, A5 & 0123456789))).

Um Zahlen zu erhalten, müssen wir nur noch die Zahlenzeichen der ersten gefundenen Zahl abrufen. Also berechnen wir die Gesamtlänge der Zeichenfolge und subtrahieren die Position der ersten gefundenen Zahl und addieren 1 dazu. Einfach. Ja, es klingt einfach komplex, es ist einfach.

Hier haben wir gerade die Gesamtlänge des Strings mit `link: / len-functio [LEN-Funktion] erhalten und dann die Position der zuerst gefundenen Zahl subtrahiert und dann 1 hinzugefügt. Dies gibt uns die Gesamtzahl der Zahlen. Erfahren Sie hier mehr über //excel-text/extract-text-from-a-string-in-excel-using-excels-left-and-right-function.html[extrahieren von Text mit den Funktionen LEFT und RIGHT von Excel.] `

Der Funktionsteil LINKS und RECHTS ist also einfach. Der knifflige Teil ist MIN und SEARCH Part, der uns die Position der zuerst gefundenen Nummer gibt. Lass uns das verstehen.

Wir wissen, wie LINKS und RECHTS funktionieren. Wir werden den Hauptteil dieser Formel untersuchen, der die Position der ersten gefundenen Zahl erhält, und zwar:

MIN (link: / text-excel-search-function [SEARCH] (\ {0,1,2,3,4,5,6,7,8,9}, String & „0123456789“)

Die Funktion link: / text-excel-search-function [SEARCH-Funktion] gibt die Position eines Textes in einer Zeichenfolge zurück.

Die Funktion SEARCH (‚text‘, ’string‘) verwendet zwei Argumente, zuerst den Text Sie möchten suchen, zweitens die Zeichenfolge, in der 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 „verkettet“ ist. 0123456789 „using & * operator. Warum? Ich werde es Ihnen sagen.

Diese Zahlen stehen am Ende daher wird kein Problem verursachen.

Lassen Sie uns unser Beispiel untersuchen. In A5 haben wir die Zeichenfolge mit dem Straßennamen und der Hausnummer. Wir müssen sie in verschiedene Zellen trennen.

Lassen Sie uns zuerst sehen, 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 in MIN (SEARCH (\ {0,1,2, 3,4,5,6,7,8,9}, ”Monta270123456789 *”))

Wie ich bereits erklärt habe, durchsucht die Suche jede Zahl im Array \ {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 und gibt ihre Position in Arrayform zurück. Das zurückgegebene Array ist \ {8,9,6,11,12,13,14,7,16,17}. Wie?

0 wird in einer Zeichenfolge gesucht. Es befindet sich an Position 8. 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 in einer Zeichenfolge gesucht und ist auch nicht Teil der ursprünglichen Zeichenfolge. Wir erhalten die Position 9.

Die nächsten 2 werden gesucht. Da es sich um den Teil der ursprünglichen Zeichenfolge handelt, erhalten wir den Index als 6.

Ebenso befindet sich jedes Element an einer Position.

Und die Geschichte danach ist ganz einfach. Wir verwenden diese Zahl, um Text und Zahlen mit der Funktion LINKS und RECHTS zu extrahieren.

Extrahieren Sie Nummer und Text aus einer Zeichenfolge, wenn sich die Nummer am Anfang der Zeichenfolge befindet. Im obigen Beispiel befand sich die Nummer 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 die Nummer am Anfang.

Hier werden wir eine andere Technik verwenden. Wir werden die Länge der Zahlen zählen (hier 2) und diese Anzahl der Zeichen links von String extrahieren.

Die Methode lautet also = LEFT (Zeichenfolge, Anzahl der Zahlen)

Um die Anzahl der Zeichen zu zählen, ist dies die Formel.

Hier ersetzt ** link: / excel-text-formeln-excel-ersatz-funktion [SUBSTITUTE-Funktion] jede gefundene Zahl durch „“ (leer). Wenn eine Zahl als Ersatz gefunden wird und eine neue Zeichenfolge zum Array hinzugefügt wird, wird eine andere 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 Zeichenfolgen zurück.

Dann subtrahieren wir von der Länge der ursprünglichen Zeichenfolgen die Länge jeder Zeichenfolge, die von der SUBSTITUTE-Funktion zurückgegeben wird. Dies gibt wieder ein Array zurück.

Jetzt addiert SUM alle diese Zahlen. Dies ist die Anzahl der Zahlen in der Zeichenfolge.

Da wir nun die Länge der Zahlen in der Zeichenfolge kennen, werden wir diese Funktion in LINKS ersetzen.

Da wir unsere Saite eine A11 haben unsere:

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 mit der rechten Funktion diese Anzahl von Zeichen aus der rechten Zeichenfolge extrahieren.

Der Hauptteil in beiden Formeln ist SUMME (LEN (A11) -LEN (ERSATZ (A11, \ {„0“, „1“, „2“, „3“, „4“, „5“, „6“,)). „7“, „8“, „9“}, „“)))

das berechnet das erste Auftreten einer Zahl. Erst wenn wir dies gefunden haben, können wir Text und Zahlen mit der LEFT-Funktion teilen. Lassen Sie uns das verstehen.

Dieser Teil gibt ein Array von Zeichenfolgen in A11 zurück, nachdem diese Zahlen durch nichts / leer („“) ersetzt wurden. Für 27Monta 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 wird von der SUBSTITUTE-Funktion zurückgegeben. Im Ergebnis haben wir \ {7,7,6,7,7,7,7,6,7,7}.

LEN (A11) -LEN (ERSATZ (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 der tatsächlichen Zeichenfolge. 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}.

SUMME (LEN (A11) -LEN (ERSATZ (A11, \ „0“, „1“, „2“, „3“, „4“, „5“, „6“, „7“, „8“) , „9“}, „“))):

Hier haben wir SUM verwendet, um das Array zu summieren, das vom obigen Teil der Funktion zurückgegeben wurde.

Dies ergibt 2. Welches ist die Anzahl der Zahlen in der Zeichenfolge.

Auf diese Weise können wir nun die Texte und Nummern extrahieren und in verschiedene Zellen aufteilen. Diese Methode funktioniert mit beiden Textarten, wenn die Zahl am Anfang und am Ende steht. Sie müssen nur die Funktionen LINKS und RECHTS entsprechend verwenden.

Die oben genannten Methoden sind etwas komplex und nicht nützlich, wenn Text und Zahlen gemischt werden. Verwenden Sie diese benutzerdefinierte Funktion, um Text und Zahlen zu teilen.

String: Der String, den Sie teilen möchten.

Op: das ist boolesch. Übergeben Sie 0 oder false, um einen Textteil zu erhalten. Übergeben Sie für den Zahlenteil true oder eine beliebige Zahl größer als 0.

Wenn sich die Zeichenfolge beispielsweise in A20 befindet,

Und

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

Dieser Code überprüft einfach jedes Zeichen in einer Zeichenfolge, ob es eine Zahl ist oder nicht. Wenn es sich um eine Zahl handelt, wird sie in der Variablen num und in der Variablen txt gespeichert. Wenn der Benutzer true für op übergibt, wird num zurückgegeben, andernfalls wird txt zurückgegeben.

Dies ist meiner Meinung nach der beste Weg, um Zahl und Text von einer Zeichenfolge zu trennen.

Sie können die Arbeitsmappe hier herunterladen, wenn Sie möchten.

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

`link: /wp-content-uploads-2019-11-Split-Number-and-Text-from-A-Cell.xls [__ Nummer und Text aus einer Zelle teilen]

link: / mail-send-and-receive-in-vba-wie-extrahiere-domänenname-aus-email-in-excel [So extrahiere ich Domainnamen aus EMail in Excel]

link: / excel-text-formeln-split-zahlen-und-text-aus-string-in-excel [Zahlen und Text aus String in Excel teilen]

link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfungen zur Steigerung Ihrer Produktivität]

link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Die VLOOKUP-Funktion in Excel]

link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016]

link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]