Remove All Numbers From Cells in Excel

Question

How can I remove all numbers from a text string in excel using formulas?

In Excel, you can use the "SUBSTITUTE" formula to remove all numbers from a text string. The formula is as follows:

=SUBSTITUTE(A1,"0","",LEN(A1)-LEN(SUBSTITUTE(A1,"0","")))

Where "A1" is the cell containing the text string. This formula uses the SUBSTITUTE function to replace all occurrences of the number "0" with an empty string. To remove other numbers you can change the "0" in the formula with the number that you want to remove.

For example, to remove all the number "1" you can use the formula: =SUBSTITUTE(A1,"1","",LEN(A1)-LEN(SUBSTITUTE(A1,"1","")))

You can also use the formula for all the digits using wildcard character: =SUBSTITUTE(A1,"[0-9]","",LEN(A1)-LEN(SUBSTITUTE(A1,"[0-9]","")))

You should repeat this formula for all digits, or use a combination of substitute and regex to remove all digits at once.

Method 2

You can use the "SUBSTITUTE" formula in combination with the "REGEXREPLACE" function to remove all numbers from a cell in Excel. The formula is as follows:

=REGEXREPLACE(A1, "[0-9]", "")

Where "A1" is the cell containing the text string. This formula uses the REGEXREPLACE function to replace all occurrences of any digit (0-9) in the cell with an empty string. This will remove all numbers from the cell.

Alternatively, you can also use the "CLEAN" function to remove all non-printable characters, including numbers, from a cell. The formula for this would be:

=CLEAN(A1)

This formula will remove all non-printable characters including numbers, from the cell.

Please note that the above formulas will only work in Excel version 365 or later, if you are using an older version of Excel, you may need to use a VBA script or macro to remove numbers from a cell.

Method 3

To remove digits and special characters from a text value in Excel, you can use the "SUBSTITUTE" formula in combination with the "REGEXREPLACE" function. Here's an example formula:

=REGEXREPLACE(A1, "[0-9!@#%^&*()_+-=[]{};':"\|,.<>/?]", "")

Where "A1" is the cell containing the text string. This formula uses the REGEXREPLACE function to replace all occurrences of digits (0-9) and special characters (!@#%^&*()_+-=[]{};':"|,.<>/) in the cell with an empty string. This will remove all digits and special characters from the cell.

Alternatively you can use the "CLEAN" function to remove all non-printable characters, including digits, from a cell. The formula for this would be:

=CLEAN(A1)

This formula will remove all non-printable characters including digits, from the cell.

Please note that the above formulas will only work in Excel version 365 or later, if you are using an older version of Excel, you may need to use a VBA script or macro to remove digits and special characters from a cell.

VBA Macro Example

A VBA macro that will remove digits from a text string in excel

Sub RemoveDigits()

'Declare variables
Dim cell As Range
Dim newString As String

'Loop through all cells in the selected range
For Each cell In Selection
    newString = ""
    'Loop through each character in the cell
    For i = 1 To Len(cell.Value)
        'Check if the character is not a digit
        If Not IsNumeric(Mid(cell.Value, i, 1)) Then
            'Add the character to the new string
            newString = newString & Mid(cell.Value, i, 1)
        End If
    Next i
    'Replace the cell value with the new string
    cell.Value = newString
Next cell

End Sub

You can run this macro by going to the "Developer" tab in Excel, then click "Visual Basic" to open the VBA editor. Then copy the above code and paste it into the editor. You can then run the macro by clicking the green "play" button on the top of the editor.

You can also assign a button to the macro in excel and run it by clicking on it.

Please note that before running the macro, you should select the cells that contain the text strings that you want to remove digits from. Also note that this macro assumes that the text strings are in the active selection. So you need to select the cells before running the macro.

ExcelDaniel Korpon