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!