So entfernen / teilen Sie Text und Zahlen in Excel Cell

Dieses Tutorial zeigt verschiedene Methoden zur Trennung von Text und Zahlen in Excel-Zellen, wobei sowohl integrierte Funktionen als auch benutzerdefinierte VBA-Funktionen verwendet werden. Sie lernen, wie Sie Zahlen extrahieren, während Sie Text entfernen, Text isolieren, während Sie Zahlen wegwerfen, und schließlich die Daten in zwei unterschiedliche Spalten aufzuteilen.

Nehmen wir an, Ihre Daten haben eine Spalte, in der Text und Zahlen vermischt sind. Für konsistente Daten können einfache Funktionen wie LEFT , RIGHT und MID ausreichen. Daten in der realen Welt sind jedoch häufig inkonsistent, wobei Zahlen vor, nach oder innerhalb von Text erscheinen. Die folgenden Lösungen befassen sich mit dieser Komplexität.

Entfernen von Text, um Zahlen zu halten (Excel 365, 2021, 2019):

Microsoft Excel 2019 hat die TEXTJOIN -Funktion eingeführt, die diese Aufgabe vereinfacht. Die folgenden Formeln nutzen TEXTJOIN , MID , ROW , INDIRECT , SEQUENCE und IFERROR , um dies zu erreichen.

Für Excel 365 und 2021:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

Für Excel 365 - 2019:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))

(In Excel 2019 erfordert dies eine STRL -Schalteingabeteilung für den Eintrag für Array -Formel. In Dynamic Array Excel reicht eine einfache Eingabetaste aus.)

Diese Formel durch jedes Zeichen iteriert und multipliziert mit 1, um Zahlen zu konvertieren. Nicht numerische Zeichen werden zu Fehlern, die von IFERROR behandelt werden, um leere Saiten zu hinterlassen. TEXTJOIN verkettet dann die verbleibenden Zahlen.

Benutzerdefinierte VBA -Funktion (alle Excel -Versionen):

Für ältere Excel -Versionen oder einen einfacheren Ansatz bietet eine benutzerdefinierte VBA -Funktion eine optimierte Lösung. Es werden zwei Versionen bereitgestellt:

VBA-Code 1 (Zeichen für Zeichen):

 Funktion removeText (STR als String)
  Dim Sres als Zeichenfolge
  sres = ""
  Für i = 1 bis len (str)
    Wenn isnumerisch ist (Mid (str, i, 1)), dann sres = sres & Mid (str, i, 1)
  Als nächstes ich
  RenoveText = sres
Endfunktion

VBA -Code 2 (regulärer Ausdruck):

 Funktion removeText (STR als Zeichenfolge) als String
  Mit createObject ("vbscript.regexp")
    .Global = true
    .Pattern = "[^0-9]"
    RenoveText = .Replace (str, "")
  Enden mit
Endfunktion

Verwenden Sie diese Funktionen mit =RemoveText(A2) . Code 2 (unter Verwendung regulärer Ausdrücke) bietet in großen Datensätzen im Allgemeinen besser.

Entfernen von Zahlen, um Text zu halten (Excel 365, 2021, 2019):

Ähnliche Formeln können beim Entfernen von Zahlen Text extrahieren. Wieder mit TEXTJOIN , MID , ROW , INDIRECT , SEQUENCE , IFERROR und ISERROR :

Für Excel 365 und 2021:

=TRIM(TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1),MID(A2,SEQUENCE(LEN(A2)),1),"")))

Für Excel 365 - 2019:

=TRIM(TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"")))

Die TRIM entfernt führende/abtrennende Räume.

Benutzerdefinierte VBA -Funktion zum Entfernen von Zahlen (alle Excel -Versionen):

Ähnlich wie bei den vorherigen VBA -Funktionen finden Sie hier zwei Versionen, um Zahlen zu entfernen und Text zu halten:

VBA-Code 1 (Zeichen für Zeichen):

 Funktion removenumbers (STR als String)
  Dim Sres als Zeichenfolge
  sres = ""
  Für i = 1 bis len (str)
    Wenn nicht ist nicht tätig (MID (STR, I, 1)), dann sres = sres & Mid (str, i, 1)
  Als nächstes ich
  Removenumbers = sres
Endfunktion

VBA -Code 2 (regulärer Ausdruck):

 Funktion removenumbers (STR als String) als String
  Mit createObject ("vbscript.regexp")
    .Global = true
    .Pattern = "[0-9]"
    Removenumbers = .replace (str, "")
  Enden mit
Endfunktion

Verwenden Sie diese mit =TRIM(RemoveNumbers(A2)) .

Teilen Sie Text und Zahlen in separate Spalten auf:

Eine einzelne benutzerdefinierte VBA -Funktion kann sowohl Text- als auch Zahlenextraktion verarbeiten:

VBA-Code 1 (Zeichen für Zeichen):

 Funktion SplittextNumbers (STR as String, is_remove_text als boolean) als String
  Dim Snum, Stext, Schar als String
  snum = stext = ""
  Für i = 1 bis len (str)
    Schar = Mid (str, i, 1)
    Wenn isnumerisch (schar) dann snum = snum & schar sonst stext = STEXT & SCHAR
  Als nächstes ich
  Wenn is_remove_text, dann splittextnumbers = snum sonst splittextnumbers = STEXT
Endfunktion

VBA -Code 2 (regulärer Ausdruck):

 Funktion SplittextNumbers (STR as String, is_remove_text als boolean) als String
  Mit createObject ("vbscript.regexp")
    .Global = true
    Wenn is_remove_text dann .pattern = "[^0-9]" else .pattern = "[0-9]"
    SplittextNumbers = .Replace (str, "")
  Enden mit
Endfunktion

Verwenden Sie =SplitTextNumbers(A2,TRUE) um Zahlen zu halten, und =TRIM(SplitTextNumbers(A2,FALSE)) um den Text aufzubewahren.

Verwenden eines speziellen Tools:

Erwägen Sie, ein dediziertes Excel-Add-In für die Charakterentfernung zu verwenden, wenn Sie einen visuelleren und benutzerfreundlicheren Ansatz bevorzugen. Das Tutorial erwähnt ein "Ultimate Suite" -Ad-In-Add-In mit einer Funktion "Entfernen von Charakteren".

Diese umfassende Anleitung bietet mehrere Lösungen für verschiedene Excel -Versionen und Benutzerpräferenzen. Denken Sie daran, Zellreferenzen nach Bedarf für Ihre spezifischen Daten anzupassen.

Das obige ist der detaillierte Inhalt vonSo entfernen / teilen Sie Text und Zahlen in Excel Cell. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!