Generische Formel
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Zusammenfassung
Um numerische Zeichen aus einer Textzeichenfolge zu entfernen, können Sie eine Formel verwenden, die auf der Funktion TEXTJOIN basiert. Im gezeigten Beispiel lautet die Formel in C5:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365.
Erläuterung
Excel hat keine Möglichkeit, die Buchstaben in einer Textzeichenfolge direkt in einer Formel in ein Array umzuwandeln. Um dieses Problem zu umgehen, verwendet diese Formel die MID-Funktion mit Hilfe der ROW- und INDIRECT-Funktionen, um das gleiche Ergebnis zu erzielen. Die kopierte Formel in C5 lautet:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Das sieht ziemlich kompliziert aus, aber das Wesentliche ist, dass wir ein Array aller Zeichen in B5 erstellen und jedes Zeichen testen, um festzustellen, ob es eine Zahl ist. In diesem Fall verwerfen wir den Wert und ersetzen ihn durch eine leere Zeichenfolge (""). Wenn nicht, fügen wir das nicht numerische Zeichen einem "verarbeiteten" Array hinzu. Schließlich verwenden wir die TEXTJOIN-Funktion (neu in Excel 2019), um alle Zeichen miteinander zu verketten und leere Werte zu ignorieren.
Mit der MID-Funktion wird der Text in B5 zeichenweise von innen nach außen extrahiert. Der Schlüssel ist das ROW- und INDIRECT-Snippet hier:
ROW(INDIRECT("1:100"))
Das dreht ein Array mit 100 Zahlen wie folgt:
(1,2,3,4,5,6,7,8… .99,100)
Hinweis: 100 steht für die maximal zu verarbeitenden Zeichen. Passen Sie es Ihren Daten an oder verwenden Sie die LEN-Funktion wie unten erläutert.
Dieses Array geht als start_num- Argument in die MID-Funktion . Für num_chars verwenden wir 1.
Die MID-Funktion gibt ein Array wie folgt zurück:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Hinweis: Zusätzliche Elemente im Array wurden aus Gründen der Lesbarkeit entfernt.
Zu diesem Array addieren wir Null. Dies ist ein einfacher Trick, der Excel zwingt, Text zu einer Zahl zu zwingen. Numerische Textwerte wie "1", "2", "3", "4" usw. werden fehlerfrei konvertiert, aber nicht numerische Werte schlagen fehl und verursachen einen # VALUE-Fehler. Wir verwenden die IF-Funktion mit der ISERR-Funktion, um diese Fehler abzufangen. Wenn wir einen Fehler sehen, wissen wir, dass wir ein nicht numerisches Zeichen haben, also bringen wir dieses Zeichen mit einer anderen MID-Funktion in das verarbeitete Array:
MID(B5,ROW(INDIRECT("1:100")),1)
Wenn Sie keine Fehlermeldung erhalten, wissen wir, dass wir eine Nummer haben, und fügen daher anstelle der Nummer eine leere Zeichenfolge ("") in das Array ein.
Das endgültige Array-Ergebnis wird als text1-Argument in die TEXTJOIN-Funktion eingegeben. Als Trennzeichen verwenden wir eine leere Zeichenfolge ("") und für ignore_empty geben wir TRUE an. TEXTJOIN verkettet dann alle nicht leeren Werte im Array und gibt das Ergebnis zurück.
Präzise Arraylänge
Anstatt eine Zahl wie 100 in INDIRECT fest zu codieren, können Sie mit der LEN-Funktion ein Array mit der tatsächlichen Anzahl von Zeichen in der Zelle wie folgt erstellen:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN gibt die Anzahl der Zeichen in der Zelle als Zahl zurück, die anstelle von 100 verwendet wird. Dadurch kann die Formel automatisch auf eine beliebige Anzahl von Zeichen skaliert werden.
Zusätzlichen Speicherplatz entfernen
Wenn Sie numerische Zeichen entfernen, bleiben möglicherweise zusätzliche Leerzeichen übrig. Um führende und nachfolgende Leerzeichen zu entfernen und Leerzeichen zwischen Wörtern zu normalisieren, können Sie die auf dieser Seite gezeigte Formel in die TRIM-Funktion einschließen:
=TRIM(formula)
Mit SEQUENZ
In Excel 365 kann die neue SEQUENCE-Funktion den obigen ROW + INDIRECT-Code ersetzen:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Hier verwenden wir SEQUENCE + LEN, um in einem Schritt ein Array mit der richtigen Länge zu erstellen.
Mit LET
Wir können diese Formel mit der LET-Funktion weiter optimieren. Da das Array oben zweimal mit SEQUENCE und LEN erstellt wurde, können wir das Array als Variable definieren und nur einmal erstellen:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Hier wird der Wert des Arrays nur einmal festgelegt und dann innerhalb der MID-Funktion zweimal verwendet.
