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 mit neuen Spielzeugen im Spiel, ich meine Funktionen.
Excel 2019 und 365 führen einige neue Funktionen (TEXTJOIN und SEQUENCE) ein, die das Entfernen numerischer Zeichen erleichtern und nur nicht numerische Werte in einer neuen Zelle abrufen können. Wir werden Formeln verwenden, die uns dabei einfacher helfen können.
Generische Formel
| =TEXTJOIN("",WAHR,WENN(ISERROR(MITTE(durcheinandergebrachter_text,REIHENFOLGE(AnzahlZeichen),1)+0),MITTE(durcheinandergebrachter_text,REIHENFOLGE(NumChars),1),""))) |
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: Numerische Zeichen entfernen und alle Alphabete und nicht numerische Zeichen extrahieren.
Hier haben wir also einen alphanumerischen Text. Dieser Text enthält einige Zahlen und einige nicht numerische Zeichen. Ich muss numerische Zeichen loswerden und nur Alphabete und andere Zeichenwerte in der Spalte D abrufen.
Ich erwarte nicht, dass die Gesamtzahl der Zeichen in einem durcheinandergebrachten Text mehr als 100 beträgt. Der Wert von NumChars ist hier also 100. Sie können diese Zahl bei Bedarf erhöhen oder verringern.
Wenden Sie hier die obige generische Formel an, um die numerischen Zeichen zu entfernen.
| =TEXTJOIN("",WAHR,WENN(ISERROR(MITTE(B3,REIHENFOLGE(100),1)+0),MITTE(B3,REIHENFOLGE(100),1),""))) |
Wenn Sie die Eingabetaste drücken, werden alle numerischen Zeichen entfernt und es bleiben nur alphabetische Werte übrig. Ziehen Sie diese Formel nach unten, um Zahlen aus der Zeichenfolge aus allen Zellen in Spalte B zu entfernen.
Wie funktioniert es?
Sehen wir uns zunächst an, wie diese Formel Schritt für Schritt gelöst wird.
| 1-> TEXTJOIN("",WAHR,WENN(ISERROR(MITTE(B3,REIHENFOLGE(100),1)+0),MITTE(B3,REIHENFOLGE(100),1),""))) |
| 2-> TEXTJOIN("",WAHR,WENN(ISERROR(MITTE("This1 is… site",{1,2,3,… 100},1)+0),MITTE(B3,REIHENFOLGE(100),1),""))) |
| 3-> TEXTJOIN("",WAHR,WENN(ISERROR({"T";"h";"i";"s";" ","1"… "";""}+0),MITTE(B3,REIHENFOLGE(100),1),""))) |
| 4-> TEXTJOIN("",WAHR,WENN(ISERROR({#WERT!;#WERT!;#WERT!;#WERT!;1…;#WERT!}),MITTE(B3,REIHENFOLGE(100),1),""))) |
| 5-> TEXTJOIN("",WAHR,WENN({WAHR;WAHR;WAHR;WAHR;FALSCH… ;WAHR},MITTE(B3,REIHENFOLGE(100),1),""))) |
| 6-> TEXTJOIN("",WAHR,{"Dies";""… .;""}) |
| 7-> "Dies ist die Website Nummer eins" |
Zur besseren Lesbarkeit habe ich nicht das ganze Array geschrieben. Ich habe Punkte (… ) verwendet, um lange Arrays anzuzeigen.
Wie Sie sehen können, beginnt die Formel von innen zu lösen. Zuerst wird die SEQUENCE-Funktion gelöst. Da haben wir 100 als Anzahl der Zeichen überschritten. Es gibt ein Array von Zahlen von 1 bis 100 zurück.
Dieses Array wird der MID-Funktion als Startnummer bereitgestellt. Die Funktion mid geht zu jedem Index im String und teilt jedes Zeichen in einem Array auf. Sie können dies in Schritt 3 sehen. Ich habe nicht das gesamte Array gezeigt, da es zu viel Platz benötigt. {"T";"h";"i";"s";" ","1"… "";""}
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. {#WERT!;#WERT!;#WERT!;#WERT!;1…;#WERT!}
Dieses Array wird an die ISERROR Funktion. Wie Sie wissen, gibt die Funktion ISERROR TRUE für Fehler und FALSE für Nicht-Fehlerwerte zurück. Daher erhalten wir ein Array von TRUE und FALSE. TRUE für nicht numerische Zeichen und FALSE für Zahlen. {WAHR;WAHR;WAHR;WAHR;FALSCH…;WAHR}.
Dasselbe passiert mit TRUE-Abschnittsanweisungen von IF (MITTE(B3,REIHENFOLGE(100),1)). Es gibt ein Array aller Zeichen einer alphanumerischen Zeichenfolge in B3 zurück.
Nun gibt die IF-Funktion für jede TRUE-Anweisung das entsprechende Zeichen aus dem Array des True-Abschnitts zurück. Bei FALSE gibt IF ein Leerzeichen ("") zurück. Jetzt haben Sie ein Array, das keine numerischen Zeichen enthält. {"Dies";""… .;""}.
Schließlich wird dieses Array von der TEXTJOIN Funktion. Diese Funktion verbindet vorgegebene Texte unter Berücksichtigung der Leerwerte mit einem vorgegebenen Trennzeichen miteinander. Daher erhalten wir einen String, der keine numerischen Zeichen enthält. Dies ist die Website Nummer eins.
Verbesserung der Formel
Die obige Formel verwendet eine hartcodierte Zahl zur Verarbeitung der Anzahl der Zeichen (wir haben 100 genommen). Aber vielleicht möchten Sie, dass es dynamisch ist. In diesem Fall haben Sie es richtig erraten, können Sie die LEN-Funktion verwenden. Es wird die genaue Anzahl von Zeichen für die Verarbeitung benötigt. Die Formel wird also sein.
| =TEXTJOIN("",TRUE,IF(ISERROR(MID(verworrener_Text,SEQUENCE(LEN(durcheinandergebrachter_text),1)+0),MITTEL(durcheinandergebrachter_text,SEQUENZ(LEN(durcheinandergebrachter_text),1),""))) |
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,IF(ISERROR(MID(verworrener_Text,REIHE(INDIREKT("1:"&LEN(AnzahlZeichen))),1)+0),MID(durcheinandergebrachter_Text,REIHE(INDIREKT("1:"&LEN(AnzahlZeichen))),1),""))) |
Der INDIREKTE wandelt den Text ("1:100") in den tatsächlichen Bereich um und die ROW-Funktion listet dann alle Zeilennummern von 1 bis 100 auf. (100 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.
So entfernen Sie nicht numerische Zeichen aus Zellen in Excel 2019: Um nicht numerische Zeichen aus einer alphanumerischen Zeichenfolge in Excel zu entfernen, verwenden wir die neue Funktion TEXTJOIN. Das Entfernen nicht numerischer Zeichen aus einer Zeichenfolge kann uns helfen, unsere Daten für eine bessere Datenanalyse zu bereinigen. So machst du es also
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.
