How to remove numbers from text in Microsoft Excel

In Microsoft Excel, removing numbers from text involves getting rid of numeric values within a text string or cell content while keeping the non-numeric characters intact. This action proves valuable when there’s a need to isolate or modify the non-numeric portion of a cell that combines both text and numbers.

In this tutorial, we will learn how to remove numbers from text in Microsoft Excel. There are multiple ways in Excel that can be adopted for removing numbers from text. These include utilizing the Flash Fill feature, and the SUBSTITUTE function or we can simply use a VBA code for this.

Here is a list showcasing names that are encoded using a specific pattern. Each person’s name is represented with a number following their first name and another number following their last name. Our objective is to extract the complete name of each individual from the given code.

Method 1: Utilizing a VBA Code

Step 1 – Press the ALT+F11 Keys

• Press the ALT+F11 keys on the keyboard.

Step 2 – Insert a New Module

• Insert a new module.
• For this, right-click on the tab name located in the menu on the left.
• Choose the Insert option and perform a click on the Module button.

Step 3 – Enter the VBA Code

• Enter the following VBA code, remember to modify the range of cells from which you want to remove numbers.

Sub RemoveNumbersFromCells()     Dim rng As Range     Dim cell As Range         ‘ Set the range where you want to remove numbers     Set rng = Range(“A1:A10”) ‘ Modify the range as per your needs         ‘ Loop through each cell in the range     For Each cell In rng         ‘ Check if the cell value is not empty         If Not IsEmpty(cell.Value) Then             Dim result As String             Dim i As Long             result = “”                         ‘ Loop through each character in the cell             For i = 1 To Len(cell.Value)                 ‘ Check if the character is not a number                 If Not IsNumeric(Mid(cell.Value, i, 1)) Then                     ‘ Append non-numeric characters to the result string                     result = result & Mid(cell.Value, i, 1)                 End If             Next i                         ‘ Update the cell value with the non-numeric characters             cell.Value = result         End If     Next cell         ‘ Clean up     Set rng = Nothing End Sub

Step 4 – Run the Code

• Run the code.
• The number from the targeted cell will be removed.

Method 2: Utilizing the SUBSTITUTE Function

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A11,1,“”),2,“”),3,“”),4,“”),5,“”),6,“”),7,“”),8,“”),9,“”),0,“”)

The formula uses nested SUBSTITUTE functions to remove numbers 0 to 9 from a range of cells (A1 to A11). Each SUBSTITUTE function replaces a specific number with an empty string. By applying these substitutions successively, the formula eliminates all occurrences of the numbers, leaving only the non-numeric characters in the cells.

Step 1 – Choose a Targeted Cell

• Choose a targeted cell where you want to remove the 

Step 2 – Enter an IF Function

• Enter an IF function in the cell.
• We will utilize the IF function to apply the SUBSTITUTE function on the whole array.

Step 3 – Enter the First Parameter of the IF Function

• Create the logical test i.e. the first parameter of the IF function.
• The logical test will be:

           IF( A2:A11<>“ ”)

• The test check is the cells are blank or not.

Step 4 – Enter the SUBSTITUTE Function as the Second Parameter

• We will utilize the SUBSTITUTE function as the second parameter of the IF function i.e. the value_if_true.

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A11,1,“”),2,“”),3,“”),4,“”),5,“”),6,“”),7,“”),8,“”),9,“”),0,“”)

• This code runs on every string, we just have to enter the range of the desired cells.
• We will enter the complete range of cells i.e. A1:A11, containing the strings so that the numbers from each code are removed at once.

Step 5 – Press the CTRL+SHIFT+ENTER Keys

• Press the CTRL+SHIFT+ENTER keys at once to apply the array formula to complete the array.
• The numbers from each string would be removed.

Method 3: Utilizing the Flash Fill Feature

Utilizing the Flash Fill feature is a convenient and efficient method for removing numbers from text. However, it is important to note that this approach is effective only when the strings containing the numbers are consistently structured or follow a predictable pattern.

Step 1 – Remove the Numbers from the First String Manually

• Remove the numbers from the first string manually.

Step 2 – Select the Next Cell

• Select the next cell in which the numbers from the second string are to be removed.

Step 3 – Press the CTRL+E Shortcut Keys

• Press the CTRL+E shortcut keys on the keyboard.
• Numbers from all the strings would be removed.