How to Remove Numbers From Text in Excel

Sometimes, you may receive Excel data where each cell in a column contains a mixture of numbers and text, as shown in the example below.

Dataset to Remove Numbers From Text in Excel

If you’re only interested in the text portion, you must remove the numbers from the text strings, so you remain with only the desired text data.

In this tutorial, I will show you ways to remove numbers from text strings with a consistent or inconsistent pattern.

Table of Contents

Method #1: Use Flash Fill to Remove Numbers From Text in Excel

You can use Excel’s Flash Fill feature to remove numbers from text strings. Flash Fill automatically fills in values based on the patterns you provide.

Flash Fill works by recognizing patterns as you type, and then applying those patterns to the rest of the data in the column.

Suppose you have the dataset below with physical addresses in column A composed of house numbers and street names. You want to remove the house numbers from the data and show only the street names in column B.

Here’s how you can remove the numbers using Flash Fill:

Select cell B2 and type in the expected output, ‘Elm Street,’, the street portion of the address in cell A2, and press Enter.
Begin to type the next expected output, ‘Maple Avenue’ in cell B3. At some point, Flash Fill detects the pattern you want to use, completes the entry for you, and offers suggestions for the next entries in gray.

If the suggested entries are correct, press Enter to accept.

If Flash Fill doesn’t give you data entry suggestions or the suggestions are incorrect, complete entering ‘Maple Avenue’ in cell B3, press Enter, and proceed with the following steps.

With cell B4 selected, open the Data tab and click the Flash Fill button on the Data Tools group.

Alternatively, press CTRL + E.

Flash Fill automatically fills in text data in column B following the example patterns you showed in cells B2 and B3.

Flash Fill automatically fills in text data Remove Numbers From Text in Excel

Flash Fill is a convenient formula-free way to remove numbers from text in Excel. However, its output is static so it doesn’t update if the original data changes.

If the original data changes, you must re-run Flash Fill to get an updated output. For dynamic output, use a formula as explained in Method #2 below.

Note: Flash Fill is available only in Excel 2013 and later versions. It’s important to review the results, as they may not always be accurate. Flash Fill often has difficulty with complex data combinations where it can’t easily identify a pattern.

Also read: How to Remove Question Marks from Text in Excel?

Method #2: Use Array Formulas to Remove Numbers From Text in Excel

I will show you two options of array formulas to remove numbers from text in Excel.

The formulas achieve the same results but use different logic to ignore numeric values in the final output.

Option #1: Use An Array Formula Combining TEXTJOIN, ISERR, ROW, and INDIRECT Functions (Excel 2019 and later)

Suppose you have the dataset below with physical addresses in column A composed of house numbers and street names.

You want to remove the house numbers from the data and show only the street names in column B.

You can use the formula below to do the job:

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

TRIM formula to Remove Numbers From Text in Excel

If you have an older version of Excel that does not have dynamic arrays, press CTRL + Shift + Enter to enter the formula. If you have Excel 365, you can just use Enter.

Drag or double-click the fill handle in cell B2 to copy the formula to other cells in the column.

The logic of the formula is as follows:

The formula checks each character in the text string in cell A2, including the space characters.
If the character is non-numeric, it is included in the final output.
If the character is numeric, it is replaced with an empty string.
Finally, the TEXTJOIN function combines all the non-numeric characters into a single text string while ignoring the empty strings. The TRIM function removes leading or trailing spaces from the resultant text string.

Here’s how the formula works:

ROW(INDIRECT(“1:” & LEN(A2))) – This segment of the formula generates an array of numbers from 1 to the length of the text in cell A2, which is {1,2,3,4,5,6,7,8,9,10,11,12,13}
MID(A2, ROW(…), 1) – This part of the formula individually extracts each character from the string in cell A2 resulting in {3,4,” “,E,l,m,” “,S,t,r,e,e,t}.
ISERR(… * 1) – This segment of the formula checks if each character is a number by attempting to multiply it by 1. If the character is a number it returns FALSE and If it’s not a number, it returns TRUE. This results in {FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}.
IF(ISERR(…), MID(…), “”) – This part of the formula keeps non-numeric characters and replaces numeric ones with empty strings. This results in {“”,””,” “,E,l,m,” “,S,t,r,e,e,t}
TRIM(TEXTJOIN(“”, TRUE, …)) – The TEXTJOIN function concatenates the remaining non-numeric characters into a text string while ignoring empty strings, the results of replacing numeric characters with empty strings by the IF function. The TRIM function removes leading or trailing spaces. This results in ‘Elm Street.’

Option #2: Use an Array Formula Combining TEXTJOIN, ISNUMBER, and SEQUENCE Functions (Excel 365 and later)

You can use the formula below for the task:

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, SEQUENCE(LEN(A2)), 1)), "", MID(A2, SEQUENCE(LEN(A2)), 1)))

TEXTJOIN and SEQUENCE formula to Remove Numbers From Text in Excel

Below is the logic of the formula:

The formula checks each character in the text string in cell A2.
If the character is numeric, the formula replaces it with an empty string.
If the character is non-numeric, the formula keeps the character.
Finally, the TEXTJOIN function concatenates the remaining non-numeric characters into a single string.

Here’s how the formula works:

SEQUENCE(LEN(A2)) generates an array of numbers from 1 to the length of the string in A2. The resulting array is {1,2,3,4,5,6,7,8,9,10,11,12,13}.
MID(A2, SEQUENCE(LEN(A2)), 1) – In this segment of the formula, the MID function extracts one character at a time from the text string in cell A2 using SEQUENCE(LEN(A2) as the position index. This results in the array {3,4,” “,E,l,m,” “,S,t,r,e,e,t}.
–MID(A2, SEQUENCE(LEN(A2)), 1) – In this part of the formula, the double unary (–) operator forces the text returned by the MID function to be converted to a number if possible. If the character is numeric, it converts it to a number. If the character is non-numeric it results in a #VALUE! error. This results in the array {3,4,#VALUE!,#VALUE!, ,#VALUE!,#VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}
ISNUMBER(–MID(…)) – In this part of the formula, the ISNUMBER function checks whether the result of –MID(…) is a number. If it is a number, the ISNUMBER function returns TRUE, otherwise it returns FALSE. This results in the array {TRUE,TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE }
IF(ISNUMBER(–MID(…)), “”, MID(…)) – In this formula segment, the IF function uses the results of the ISNUMBER function to decide what to return. If the result of ISNUMBER is TRUE the IF function returns an empty string. If it is FALSE the IF function returns the original text character. This results in {“”,””,” “,E,l,m,” “,S,t,r,e,e,t}
TEXTJOIN(“”, TRUE, …) – The TEXTJOIN function concatenates all the non-numeric characters the IF function returns into a single string without spaces. The second TRUE argument tells TEXTJOIN to ignore empty strings, the results of replacing numeric characters with empty strings by the IF function. The TRIM function removes leading and trailing spaces from the output. This results in ‘Elm Street.’

Also read: How to Extract Number from Text in Excel (Beginning, End, or Middle)

Method #3: Use Power Query To Remove Numbers From Text in Excel

You can use Power Query to remove numbers from text in Excel. Power Query is a feature that allows users to discover, connect, clean, and manipulate data from various sources within Excel.

Suppose you have the below dataset of physical addresses composed of house numbers and street names. You want to remove the house numbers from the addresses and remain with only street names.

You can use Power Query to do the job but first convert the data range to a table or a named range. I have converted it to the Excel table below by selecting a cell in the dataset and pressing CTRL + T.

Here’s how to use Power Query to remove numbers from the text:

Select a cell in the Excel table.
Click the Data tab, open the Get Data drop-down list on the Get & Transform Data group, hover over the ‘From Other Sources’ option, and click ‘From Table/Range’ on the submenu.

The above step opens the Power Query Editor.

Select the column where you want to remove numbers from text on the Power Query Editor. In this case, the column is already selected because my table has only one column.
Click the Home tab, open the Split Column drop-down list, and select the ‘By Digit to Non-Digit’ option.

select the ‘By Digit to Non-Digit’ option

Note: I have chosen the ‘By Digit to Non-Digit’ option because my dataset starts with numbers. If the numbers were at the end, I would select the ‘By Non-Digit to Digit’ option instead.

The above step splits the column into two columns, one with only numbers and the other with only text.

Select the column with numbers by clicking its header and clicking the ‘Remove Columns’ button on the Home tab.

clicking the ‘Remove Columns’ button on the Home tab

The above step removes from the table the selected column with numbers.

Optionally, you can rename the column by selecting its title and replacing it with the new title.
Open the Close & Load drop-down list and select the Close & Load option to save changes to the query, close the Query Editor window, and load the results to a new worksheet, the default destination. If you select the ‘Close & Load to’ option, it opens the Import Data dialog box allowing you to specify where you want to load the results.

In this case, I choose the Close & Load option to load the results to a new worksheet.

Note: The source Excel table is linked to the output Excel table. So, if you enter new data in the source table, all you need to do to get updated output is right-click any cell in the output table and click ‘Refresh’ on the shortcut menu, and Power Query will run in the background and give you the desired results.

Method #4: Use a User-Defined Function (UDF) to Remove Numbers From Text in Excel

You can use a custom VBA function to remove numbers from text in Excel.

You can use the steps below to create a UDF in VBA and use it to do the job:

Press ALT + F11 to open the VB Editor.
Insert a standard module.
Copy the code below to the standard module.

Function GETTEXT(cell As Range) As String
Dim i As Integer
Dim result As String
Dim char As String
Dim prevCharIsLetter As Boolean
result = ""
prevCharIsLetter = False
For i = 1 To Len(cell.Value)
char = Mid(cell.Value, i, 1)
If UCase(char) Like "[A-Z]" Then
If Not prevCharIsLetter And Len(result) > 0 Then
result = result & " "
End If
result = result & char
prevCharIsLetter = True
Else
prevCharIsLetter = False
End If
Next i
GETTEXT = result
End Function

Save the workbook as a Macro-Enabled Workbook (*.xlsm).
Press ALT + F11 to switch to the worksheet containing the dataset.
Enter the formula ‘=GETTEXT(A2)’ in cell A2 and drag or double-click the fill handle to copy the formula to other cells in the column.

GETTEXT formula to Remove Numbers From Text in Excel

The UDF checks each character in the target cell. If it finds a letter it adds it to the result string in the ‘result’ variable. It also checks if the previous character was a letter. If not and the ‘result’ variable is not empty, it adds a space before appending a new set of characters.

Using the Personal Macro Workbook (PERSONAL.XLSB)

If you want to use the UDF across all workbooks, copy it to the Personal Macro Workbook (PERSONAL.XLSB).

You can create a Personal Macro Workbook by recording an empty macro, using the steps below:

Open the Developer tab and click the Record Macro option on the Code group.

On the Record Macro dialog box, open the ‘Store macro in’ drop-down list and select the ‘Personal Macro Workbook’ option.

Click OK.
Save and close all open Excel files. Click Save on the message box prompting you to save the changes you have made to the Personal Macro Workbook.

From now on you will be able to see and access the Personal Macro Workbook in the Project Explorer Window of the VB Editor.

After creating the Personal Macro Workbook, replace the empty macro with the User Defined Function (UDF).

To use the UDF you have copied to the Personal Macro Workbook in a workbook other than the one in which you created it, for instance in cell A2, enter it in the format ‘PERSONAL.XLSB!GETTEXT(A2)’ as shown below.

I have shown you four ways to remove numbers from text in Excel. I hope you found the tutorial helpful.

Other articles you may also like: