Wir haben gelernt, wie man in Excel 2016 und älter numerische Werte aus einer Zelle entfernt. Die von uns verwendeten Formeln waren etwas komplex, aber jetzt sind Excel 2019 und 365 im Spiel.
Excel 2019 und 365 führen einige neue Funktionen ein, die das Entfernen nicht numerischer Zeichen erleichtern und nur numerische Werte in einer neuen Zelle abrufen. Wir werden Formeln verwenden, die uns dabei einfacher helfen können.
Generische Formel
| =TEXTJOIN("",WAHR,WENNFEHLER(MITTE(durcheinandergebrachter_text,REIHENFOLGE(NumChars),1)+0,"")) |
Durcheinandergebrachter_text: Dies ist der Quelltext, aus dem Sie alle numerischen Werte extrahieren möchten.
NumChars: Dies ist die Gesamtzahl der Zeichen, die Sie verarbeiten möchten. Der jumbled_text sollte nicht mehr Zeichen als diese Zahl haben (Zeichen und Zahlen kombiniert).
Sehen wir uns ein Beispiel an, um die Dinge zu verdeutlichen.
Beispiel: Nicht numerische Zeichen entfernen und alle Zahlen extrahieren
Hier haben wir also einen durcheinandergebrachten Text. Dieser Text enthält einige Zahlen und einige nicht numerische Zeichen. Ich muss nicht numerische Zeichen loswerden und numerische Werte nur in der D-Spalte erhalten.
Ich erwarte nicht, dass die Gesamtzahl der Zeichen in einem durcheinandergebrachten Text mehr als 20 beträgt. Der Wert von NumChars beträgt hier also 20. Sie können diese Zahl bei Bedarf erhöhen.
Wenden Sie hier die obige generische Formel an, um die nicht numerischen Zeichen zu entfernen.
| =TEXTJOIN("",WAHR,WENNFEHLER(MITTE(C3,REIHENFOLGE(20),1)+0,"")) |
Und wenn Sie die Eingabetaste drücken. Sie erhalten alle nicht numerischen Zeichen entfernt. Ziehen Sie diese Formel nach unten, um Zeichen aus der Zeichenfolge aus allen Zellen in Spalte C3 zu entfernen.
Wie funktioniert es?
Sehen wir uns zunächst an, wie diese Formel Schritt für Schritt gelöst wird.
| 1-> TEXTJOIN("",WAHR,WENNFEHLER(MITTE(C3,REIHENFOLGE(20),1)+0,"")) |
| 2-> TEXTJOIN("",WAHR,WENNFEHLER(MITTE("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEXTJOIN("",WAHR,WENNFEHLER({"1";"2";"a";"s";"w";"1";"2";"w";"1";"2";"3";"";"";"";"";"";"";"";"";""}+0,"")) |
| 4-> TEXTJOIN("",WAHR,WENNFEHLER({1;2;#WERT!;#WERT!;#WERT!;1;2;#WERT!;1;2;3;#WERT!;#WERT!;… ;#WERT!}+0,"")) |
| 5-> TEXTJOIN("",WAHR,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Wie Sie sehen können, beginnt die Formel von innen zu lösen. Zuerst wird die SEQUENCE-Funktion gelöst. Da wir 20 überschritten haben. Es gibt ein Array von Zahlen von 1 bis 20 zurück.
Dieses Array wird der MID-Funktion als Startnummer bereitgestellt. Die mid-Funktion geht zu jedem Index im String und teilt jedes Zeichen auf. Das sehen Sie in Schritt 3.
Als nächstes fügen wir jedem Zeichen 0 hinzu. Wenn Sie in Excel versuchen, Zahlen zu nicht numerischen Zeichen hinzuzufügen, führt dies zu #VALUE! Fehler. Wir erhalten also ein Array von Zahlen und #VALUE! Fehler. Die nicht numerischen Zeichen sind jetzt weg.
Die nächste IFERROR-Funktion ersetzt alle #VALUE-Fehler durch "" (leer). Jetzt bleiben uns numerische Werte und Leerzeichen.
Schließlich wird dieses Array an die TEXTJOIN-Funktion geliefert. Die Funktion TEXTJOIN verkettet sie und wir erhalten die Zeichenfolge a, die nur Zahlen enthält.
Verbesserung der Formel
Die obige Formel verwendet eine hartcodierte Zahl zur Verarbeitung der Anzahl der Zeichen (wir haben zwanzig genommen). Aber vielleicht möchten Sie, dass es dynamisch ist. In diesem Fall haben Sie es richtig erraten, wir verwenden die LEN-Funktion. Für die Verarbeitung wird eine genaue Anzahl von Zeichen benötigt. Die Formel wird also sein.
| =TEXTJOIN("",TRUE,IFERROR(MID(verworrener_Text,SEQUENCE(LEN(durcheinandergebrachter_text)),1)+0,"")) |
Hier erkennt die LEN-Funktion automatisch die genauen Zahlenzeichen in der alphanumerischen Zeichenfolge. Dies entlastet die Ermittlung der maximalen Zeichenanzahl.
Alternative der SEQUENCE-Funktion
Wenn Sie die SEQUENCE-Funktion nicht verwenden möchten, können Sie eine Kombination aus ROW- und INDIRECT-Funktion verwenden, um fortlaufende Nummern zu generieren.
| =TEXTJOIN("",TRUE,IFERROR(MID(verworrener_Text,REIHE(INDIREKT("1:"&LEN(Jumbled_Text))),1)+0,"")) |
Der INDIREKTE wandelt den Text ("1:20") in den tatsächlichen Bereich um und die ROW-Funktion listet dann alle Zeilennummern von 1 bis 20 auf. (20 ist nur ein Beispiel. Es kann eine beliebige Zahl sein).
Also ja, Leute, so können Sie die nicht numerischen Zeichen aus einer alphanumerischen Zeichenfolge in Excel entfernen. Ich hoffe, ich war erklärend genug und dieser Artikel hat dir geholfen. Wenn Sie Fragen zu diesem Thema oder einem anderen Excel/VBA-Thema haben. Bis dahin bleiben Sie Excelling.
Teilen Sie Zahlen und Text aus einer Zeichenfolge in Excel 2016 und älter auf: Als wir keine TEXTJOIN-Funktion hatten, haben wir die LEFT- und RIGHT-Funktionen mit einigen anderen Funktionen verwendet, um numerische und nichtnumerische Zeichen aus einer Zeichenfolge zu trennen.
Extrahieren Sie Text aus einer Zeichenfolge in Excel mit der LINKS- und RECHTS-Funktion von Excel: Um Text in Excel aus einer Zeichenfolge zu entfernen, können wir die LINKS- und RECHTS-Funktion von Excel verwenden. Diese Funktionen helfen uns, Saiten dynamisch zu zerhacken.
Entfernen Sie führende und nachgestellte Leerzeichen aus dem Text in Excel: Führende und nachgestellte Leerzeichen sind visuell schwer zu erkennen und können Ihre Daten durcheinander bringen. Das Entfernen dieser Zeichen aus der Zeichenfolge ist eine grundlegende und wichtigste Aufgabe bei der Datenbereinigung. So können Sie dies ganz einfach in Excel tun.
Zeichen von rechts entfernen: Um Zeichen rechts von einer Zeichenfolge in Excel zu entfernen, verwenden wir die LEFT-Funktion. Ja, die LINKE Funktion. Die LEFT-Funktion behält die angegebene Anzahl von Zeichen von LEFT bei und entfernt alles von rechts.
Entfernen Sie unerwünschte Zeichen in Excel: Um unerwünschte Zeichen aus einer Zeichenfolge in Excel zu entfernen, verwenden wir die Funktion SUBSTITUTE. Die Funktion SUBSTITUTE ersetzt die angegebenen Zeichen durch ein anderes gegebenes Zeichen und erzeugt eine neue geänderte Zeichenfolge.
So entfernen Sie Text in Excel ausgehend von einer Position in Excel: Um Text von einer Startposition in einem String zu entfernen, verwenden wir die REPLACE-Funktion von Excel. Diese Funktion hilft uns, die Startposition und die Anzahl der zu entfernenden Zeichen zu bestimmen.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
So verwenden Sie die Excel SVERWEIS-Funktion| Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie Excel ZÄHLENWENN-Funktion| Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.
