This tutorial demonstrates several methods for separating text and numbers within Excel cells, utilizing both built-in functions and custom VBA functions. You'll learn how to extract numbers while removing text, isolate text while discarding numbers, and finally, split the data into two distinct columns.

Let's say your data has a column where text and numbers are intermingled. For consistent data, simple functions like LEFT, RIGHT, and MID might suffice. However, real-world data is often inconsistent, with numbers appearing before, after, or within text. The solutions below address this complexity.

Removing Text to Keep Numbers (Excel 365, 2021, 2019):

Microsoft Excel 2019 introduced the TEXTJOIN function, which simplifies this task. The formulas below leverage TEXTJOIN, MID, ROW, INDIRECT, SEQUENCE, and IFERROR to achieve this.

For Excel 365 and 2021:

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

For Excel 365 - 2019:

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

(In Excel 2019, this requires Ctrl Shift Enter for array formula entry. In dynamic array Excel, a simple Enter suffices.)

This formula iterates through each character, multiplying by 1 to convert numbers; non-numeric characters become errors, handled by IFERROR to leave empty strings. TEXTJOIN then concatenates the remaining numbers.

Custom VBA Function (All Excel Versions):

For older Excel versions or a simpler approach, a custom VBA function offers a streamlined solution. Two versions are provided:

VBA Code 1 (Character-by-Character):

Function RemoveText(str As String)
  Dim sRes As String
  sRes = ""
  For i = 1 To Len(str)
    If IsNumeric(Mid(str, i, 1)) Then sRes = sRes & Mid(str, i, 1)
  Next i
  RemoveText = sRes
End Function

VBA Code 2 (Regular Expression):

Function RemoveText(str As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^0-9]"
    RemoveText = .Replace(str, "")
  End With
End Function

Use these functions with =RemoveText(A2). Code 2 (using regular expressions) generally performs better on large datasets.

Removing Numbers to Keep Text (Excel 365, 2021, 2019):

Similar formulas can extract text while removing numbers. Again, using TEXTJOIN, MID, ROW, INDIRECT, SEQUENCE, IFERROR, and ISERROR:

For Excel 365 and 2021:

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

For 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),"")))

The TRIM function removes leading/trailing spaces.

Custom VBA Function for Removing Numbers (All Excel Versions):

Similar to the previous VBA functions, here are two versions to remove numbers and keep text:

VBA Code 1 (Character-by-Character):

Function RemoveNumbers(str As String)
  Dim sRes As String
  sRes = ""
  For i = 1 To Len(str)
    If Not IsNumeric(Mid(str, i, 1)) Then sRes = sRes & Mid(str, i, 1)
  Next i
  RemoveNumbers = sRes
End Function

VBA Code 2 (Regular Expression):

Function RemoveNumbers(str As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[0-9]"
    RemoveNumbers = .Replace(str, "")
  End With
End Function

Use these with =TRIM(RemoveNumbers(A2)).

Splitting Text and Numbers into Separate Columns:

A single custom VBA function can handle both text and number extraction:

VBA Code 1 (Character-by-Character):

Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String
  Dim sNum, sText, sChar As String
  sNum = sText = ""
  For i = 1 To Len(str)
    sChar = Mid(str, i, 1)
    If IsNumeric(sChar) Then sNum = sNum & sChar Else sText = sText & sChar
  Next i
  If is_remove_text Then SplitTextNumbers = sNum Else SplitTextNumbers = sText
End Function

VBA Code 2 (Regular Expression):

Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    If is_remove_text Then .Pattern = "[^0-9]" Else .Pattern = "[0-9]"
    SplitTextNumbers = .Replace(str, "")
  End With
End Function

Use =SplitTextNumbers(A2,TRUE) to keep numbers, and =TRIM(SplitTextNumbers(A2,FALSE)) to keep text.

Using a Specialized Tool:

Consider using a dedicated Excel add-in for character removal if you prefer a more visual and user-friendly approach. The tutorial mentions an "Ultimate Suite" add-in with a "Remove Characters" feature.

This comprehensive guide provides multiple solutions catering to different Excel versions and user preferences. Remember to adjust cell references as needed for your specific data.

The above is the detailed content of How to remove / split text and numbers in Excel cell. For more information, please follow other related articles on the PHP Chinese website!