Direkt zum Inhalt

Entfernen oder entfernen Sie nicht numerische Zeichen aus Textzeichenfolgen

Manchmal müssen Sie möglicherweise alle nicht numerischen Zeichen aus den Textzeichenfolgen entfernen und nur die Zahlen wie im folgenden Screenshot gezeigt beibehalten. In diesem Artikel werden einige Formeln zum Lösen dieser Aufgabe in Excel vorgestellt.

Entfernen oder entfernen Sie alle nicht numerischen Zeichen mit Formeln aus Textzeichenfolgen

In Excel 2019 und Office 365 können Sie mit einer neuen TEXTJOIN-Funktion in Kombination mit den Funktionen IFERROR, MID, ROW und INDIRECT nur Zahlen aus einer Textzeichenfolge extrahieren. Die generische Syntax lautet:

=TEXTJOIN("",TRUE,IFERROR(MID(text,ROW(INDIRECT("1:100")),1)+0,""))
  • text: Die Textzeichenfolge oder der Zellenwert, aus dem alle nicht numerischen Zeichen entfernt werden sollen.

1. Bitte kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein, in der Sie das Ergebnis ausgeben möchten:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

2. Und dann drücken Strg + Umschalt + Enter Tasten zusammen, um das erste Ergebnis zu erhalten, siehe Screenshot:

3. Wählen Sie dann die Formelzelle aus und ziehen Sie den Füllpunkt nach unten zu den Zellen, auf die Sie diese Formel anwenden möchten. Es wurden nur Zahlen extrahiert und alle anderen nicht numerischen Zeichen entfernt (siehe Abbildung):

Erklärung der Formel:

REIHE (INDIREKT ("1: 100"))： Nummer 1: 100 in der INDIRECT-Formel bedeutet, dass die MID-Funktion 100 Zeichen der Textzeichenfolge auswertet. Dieses Array enthält 100 Zahlen wie diese: {1; 2; 3; 4; 5; 6; 7; 8 .... 98; 99; 100}.
Note: Wenn Ihre Textzeichenfolge viel länger ist, können Sie die Nummer 100 nach Bedarf in eine größere Nummer ändern.

MITTE (A2, REIHE (INDIREKT ("1: 100"))), 1: Diese MID-Funktion wird verwendet, um Text in Zelle A2 zu extrahieren, um ein Zeichen zu erhalten, und es wird ein Array wie das folgende erstellt:
{"5"; "0"; "0"; ""; "K"; "u"; "t"; "o"; "o"; "l"; "s"; ""; "f" ; "o"; "r"; ""; "E"; "x"; "c"; "e"; "l"; ""; ""; ""; ""; ""; ""; "". ..}

MID(A2,ROW(INDIRECT("1:100")),1)+0: Wenn Sie den Wert 0 hinzufügen, nachdem dieses Array verwendet wurde, um den Text in eine Zahl zu zwingen, wird der numerische Textwert in eine Zahl konvertiert und nicht numerische Werte werden wie folgt als # VALUE-Fehlerwert angezeigt:
{"5"; "0"; "0"; # VALUE !; # VALUE !; # VALUE !; # VALUE !; # VALUE !; # VALUE! !;#WERT! !;#WERT!...}

IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0: Diese IFERROR-Funktion wird verwendet, um alle Fehlerwerte durch eine leere Zeichenfolge wie folgt zu ersetzen:
{"5"; "0"; "0"; ""; ""; ""; ""; ""; ""; ""; ""; ""; …}

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): Zuletzt kombiniert diese TEXTJION-Funktion alle nicht leeren Werte im Array, die von der IFFERROR-Funktion zurückgegeben wurden, und gibt das Ergebnis zurück.

Notizen:

1. Mit der obigen Formel werden die Zahlen als Textformat zurückgegeben. Wenn Sie einen echten numerischen Wert benötigen, wenden Sie diese Formel an. Denken Sie daran, zu drücken Strg + Umschalt + Enter Tasten zusammen, um das richtige Ergebnis zu erhalten.

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))+0

2. In früheren Excel-Versionen funktioniert diese Formel nicht. In diesem Fall kann Ihnen die folgende Formel helfen. Kopieren Sie diese Formel oder geben Sie sie in eine leere Zelle ein:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Entfernen oder entfernen Sie alle nicht numerischen Zeichen mit einer einfachen Funktion aus Textzeichenfolgen

Vielleicht ist es zu lang, um sich an die obigen Formeln zu erinnern. Hier werde ich die vorstellen Kutools for Excel für Sie mit seiner Zeichen entfernen Mit dieser Funktion können Sie numerische, alphabetische, nicht druckbare oder alphanumerische Zeichen mit nur wenigen Klicks aus Textzeichenfolgen entfernen. Klicken Sie hier, um Kutools for Excel kostenlos herunterzuladen!

Verwendete relative Funktionen:

  • TEXTJOIN:
  • Die TEXTJOIN-Funktion verknüpft mehrere Werte aus einer Zeile, Spalte oder einem Zellbereich mit einem bestimmten Trennzeichen.
  • MID:
  • Die MID-Funktion wird verwendet, um eine bestimmte Anzahl von Zeichen aus der Mitte einer bestimmten Textzeichenfolge zu finden und zurückzugeben.
  • ROW:
  • Die Excel ROW-Funktion gibt die Zeilennummer einer Referenz zurück.
  • INDIRECT:
  • Die Excel INDIRECT-Funktion konvertiert eine Textzeichenfolge in eine gültige Referenz.
  • IFERROR:
  • Die IFERROR-Funktion wird verwendet, um ein benutzerdefiniertes Ergebnis zurückzugeben, wenn eine Formel einen Fehler auswertet, und um ein normales Ergebnis zurückzugeben, wenn kein Fehler auftritt.

Weitere Artikel:

Die besten Tools für die Office-Produktivität

Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben

🤖 Kutools KI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Erstellen Sie benutzerdefinierte Formeln  |  Analysieren Sie Daten und erstellen Sie Diagramme  |  Rufen Sie Kutools-Funktionen auf...
Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate  |  Leere Zeilen löschen  |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren  |  Runde ohne Formel ...
Super VLookup: Mehrere Kriterien  |  Mehrfachwert  |  Über mehrere Blätter hinweg  |  Unscharfe Suche...
Adv. Dropdown-Liste: Einfache Dropdown-Liste  |  Abhängige Dropdown-Liste  |  Mehrfachauswahl Dropdown-Liste...
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  |  Spalten vergleichen mit Wählen Sie Gleiche und Unterschiedliche Zellen ...
Ausgewählte Funktionen: Rasterfokus  |  Designansicht  |  Große Formelleiste  |  Arbeitsmappen- und Blattmanager | Ressourcen (Autotext)  |  Datumsauswahl  |  Arbeitsblätter kombinieren  |  Zellen verschlüsseln/entschlüsseln  |  Senden Sie E-Mails nach Liste  |  Superfilter  |  Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets:  12 Text Tools (Text hinzufügen, Zeichen entfernen ...)  |  50+ Chart Typen (Gantt-Diagramm ...)  |  40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag ...)  |  19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen ...)  |  12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion ...)  |  7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Teilen Sie Excel-Zellen ...)  |  ... und mehr

Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...

Beschreibung

Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).

  • Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
  • Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
  • Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
  • Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
Comments (4)
No ratings yet. Be the first to rate!
Sanae
Sanae
This comment was minimized by the moderator on the site
What is this formula for french settings?
Reply
0
0
Glenn777
Glenn777
This comment was minimized by the moderator on the site
Thanks for this. Nice formula.How would I alter it so that if the cell contains only letters the formula enters a 0 the results cell (rather than just blank as it is at the moment)?Thought I might be able to do it by wrapping the formula in another IF statement but I’m not getting very far.
Reply
Report
0
0
skyyang
skyyang     Glenn777
This comment was minimized by the moderator on the site
Hello, Glenn,To display the results as blanks rather than zeros, please apply the following formula:=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

Please try, hope it can help you!
Reply
Report
0
0
Glenn777
Glenn777     skyyang
This comment was minimized by the moderator on the site
Wow that’ll take some digesting 😋 Thanks for taking the time to reply 👍
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations