How to Remove Numbers from a Cell in Excel

Let’s say you have a list of products, and each cell contains both the product name and a number, like “Laptop123” or “Phone456.” You must clean up this list by removing the numbers so only the product names remain.

It will make your inventory report look neater and more professional. Let’s see how you can do this in Excel.

There are four major ways to remove numbers from a cell or a range of cells in Excel.

—remove-numbers—

Use SUBSTITUTE Function to Remove Numbers from a Cell

SUBSTITUTE function replaces specific text in a cell with new text. It’s useful for changing or removing a value of the text to clean up or update it

In this formula, we have used SUBSTITUTE ten times to remove all the 10 numbers individually. You can use this formula in any of the Excel versions.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")

This formula removes all numbers from a cell by replacing each digit (0 through 9) with a space. Starting from 0, it substitutes each number with no value to delete it. After going through all the digits, the cell will only have the text values in the result.

Remove Numbers with SUBSTITUTE, SEQUENCE, and TEXTJOIN

SEQUENCE and TEXTJOIN are two new functions in Excel, available in Excel 2019 (Or Above) and Excel 365.

SEQUENCE generates a list of numbers in order. You can specify how many numbers you need, starting from any number you choose. TEXTJOIN combines multiple texts into one, using a separator you choose. It helps combine values while adding spaces, commas, or other characters between the combined text.

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

To understand this function, you need to split it into parts and check what each part does:

• SEQUENCE(LEN(A2)): This creates a list of numbers from 1 to the length of the text in cell A2.
• MID(A2, SEQUENCE(LEN(A2)), 1): This extracts each character from A2, one by one.
• ISNUMBER(–MID(A2, SEQUENCE(LEN(A2)), 1)): This checks if each character is a number.
• IF(ISNUMBER(…), “”, MID(…)): If the character is a number, it replaces it with nothing; if it’s not a number, it keeps the character.
• TEXTJOIN(“”, TRUE, IF(…)): This combines all the kept characters into one string without any separators, removing the numbers.

In simple words, it checks each character in the cell (A2) one by one. If a character is a number, it ignores it; if it’s not a number, it keeps it. Then, it combines all the characters kept in one string without any separators.

Combine TEXTJOIN and MID to Remove Numbers

You can also use a combination of functions like TEXTJOIN and MID to create an array formula that can remove all the numbers from a value in a cell.

To understand this formula, you need to split it into parts and then understand each part and how that part works:

• INDIRECT(“1:” & LEN(A2)) creates a list of numbers from 1 to the length of the text in cell A2.
• ROW(INDIRECT(“1:” & LEN(A2))) creates a list of row numbers, essentially 1, 2, 3, …, up to the length of A2.
• MID(A2, ROW(…), 1) gets each character from A2, one by one.
• VALUE(MID(…)) tries to convert each character to a number.
• IF(ISERR(VALUE(…)), MID(…), “”) when converting a character to a number returns an error (meaning it’s not a number), it keeps the character; and if it doesn’t, it replaces it with nothing.
• TEXTJOIN(“”, TRUE, IF(…)) combines all the characters (which gets when an error occurs) into one text without any separators, removing the numbers.

This formula might not be the best to use as it is way more complex than all the other two discussed above. So, you can only use it when you don’t want to use any of the above formulas.

Create a Custom Function to Remove Numbers

If you don’t want to use complex formulas and frequently need to remove numbers from a value, it’s better to create a custom function using code.

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

To create this custom function, open the VBA editor by pressing Alt + F11. You can insert a new module by right-clicking on any of the items in the “VBAProject” pane and selecting Insert > Module. Copy and paste the code into the module.

Using Flash Fill to Remove Numbers (Not a Dynamic method)

Flash Fill automatically removes numbers from a value when it detects a pattern. Here’s how you can use Flash Fill:

If you have a value in column A starting from cell A2, select cell B2 and then enter a value other than numbers. In the example below, my value is “Laptop123”, so I need to type only “Laptop” here.

After that, select cell B3, the next cell from B2, and press the keyboard shortcut Ctrl + E. You can also go to the Data tab, and in the data tools, click on the “Flash Fill” button.

The moment you press the button, it will use the pattern you have used above and then remove numbers from the rest of the values.

In the end…

You can use any of the methods discussed above, depending on your needs. But if you ask me, I’d go with the first two formulas. And if I need to do it frequently, then I’ll go with a custom function.

Make sure to have a backup workbook before you use any of the methods. Please note that functions like TEXTJOIN and SEQUENCE are only available in newer versions (Excel 365 and 2019).

Flash Fill is a quick and easy way to remove numbers, but it’s not a dynamic method; you need to do it again for more data.