Removing Numbers from Text in Excel: A Step-by-Step Guide

To remove numbers from text in Excel, the most reliable method for modern versions is using a combination of LET, MID, and SEQUENCE to filter characters. This approach iterates through each character, checks if it is numeric, and retains only letters or symbols. For older Excel versions without dynamic arrays, a VBA User Defined Function provides an equally robust solution. These techniques ensure that data cleaning processes remain accurate regardless of the specific version of Microsoft Excel you are utilizing.

The Modern Formula Approach

The previous attempt using TEXTAFTER failed because that function is designed to extract text after a specific delimiter, not to filter individual characters based on a property like "is number." It does not support array-based filtering logic natively. Instead, we must construct a formula that evaluates every single character in the string. For users with Excel 365 or Excel 2021+, the following formula achieves this by breaking the text into individual characters, testing them, and reassembling the result. Enter this formula in cell B2, assuming your source text is in A2:

=LET(
    text, A2,
    len, LEN(text),
    chars, MID(text, SEQUENCE(len), 1),
    isNum, ISNUMBER(chars+0),
    keep, IF(NOT(isNum), chars, ""),
    CONCAT(keep)
)

This formula uses the LET function to define variables for clarity and performance. First, it calculates the length of the text string. Then, it uses SEQUENCE to generate an array of positions from 1 to the length of the string. The MID function extracts each character one by one. The critical step is ISNUMBER(chars+0). By adding zero to the text characters, Excel attempts to convert them to numbers. If the character is a digit (0-9), the conversion succeeds, and ISNUMBER returns TRUE. If it is a letter or symbol, the conversion fails, returning FALSE. We then use IF to keep the character if it is NOT a number, otherwise replacing it with an empty string. Finally, CONCAT joins all the remaining non-numeric characters back together into a single string. If you prefer a slightly more compact version without LET, you can nest these functions directly, though it may be harder to read:

=CONCAT(IF(NOT(ISNUMBER(MID(A2,SEQUENCE(LEN(A2)),1)+0)),MID(A2,SEQUENCE(LEN(A2)),1),""))

Note that this formula will also remove spaces if you consider spaces as part of the "text" you want to strip, but typically numbers refer to digits 0-9. If you need to preserve spaces, you should modify the condition to explicitly check for digits rather than just checking if the value is numeric. However, for standard alphanumeric cleaning, the above method is highly effective.

Handling Edge Cases and Spaces

When removing numbers, you might notice that leading or trailing spaces can sometimes cause issues if the original data was padded. Additionally, some users may want to remove numbers even if they are embedded within words, such as turning "Item123" into "Item". The formula provided above handles this seamlessly because it operates on a character-by-character basis. It is important to remember that this formula creates a dynamic array output. In Excel 365, this spills automatically to adjacent cells. If you are copying this formula down a column, ensure there is enough empty space below the first instance to allow the spill range. If you encounter a #SPILL! error, clear any data blocking the range. For those who find dynamic arrays confusing or are working in a shared environment where formulas might behave unexpectedly, the next section details a VBA alternative that works in all versions of Excel. You can also look at Convert Multiple Lines to Single Cell in Excel if you need to consolidate results after cleaning your data.

VBA Solution for All Excel Versions

If you are using Excel 2019 or earlier, or simply prefer a custom function that behaves like a native worksheet function, VBA is the best path. This method is faster for large datasets and does not rely on volatile array operations. To implement this, press Alt + F11 to open the Visual Basic Editor. Insert a new module via the Insert menu and paste the following code:

Function RemoveNumbers(ByVal txt As String) As String
    Dim i As Long
    Dim charCode As Integer
    Dim result As String
    
    result = ""
    
    For i = 1 To Len(txt)
        charCode = Asc(Mid(txt, i, 1))
        
        ' Check if character is NOT a digit (ASCII 48-57)
        If charCode < 48 Or charCode > 57 Then
            result = result & Mid(txt, i, 1)
        End If
    Next i
    
    RemoveNumbers = result
End Function

This function loops through each character in the input string. It checks the ASCII value of each character. Digits '0' through '9' have ASCII values between 48 and 57. If the character's ASCII code falls outside this range, it is appended to the result string. Otherwise, it is skipped. Once saved, you can use this function in your spreadsheet just like any other formula. Simply enter =RemoveNumbers(A2) in cell B2. This is particularly useful if you have thousands of rows to clean, as VBA functions are generally more efficient than complex array formulas in older Excel versions.

Comparing Methods and Best Practices

Choosing between the formula and VBA methods depends on your specific needs. The LET formula is ideal for quick, ad-hoc cleaning tasks in modern Excel environments. It requires no macro-enabled workbooks and is easy to audit. However, it can be slow on very large datasets due to the creation of intermediate arrays. The VBA approach is superior for performance and compatibility. It works in Excel 2007 through 365 and does not create memory overhead associated with dynamic arrays. However, it requires saving the file as a Macro-Enabled Workbook (.xlsm), which may be a security concern in some corporate environments. Regardless of the method chosen, always test your cleaning process on a small sample dataset before applying it to your entire database. Incorrectly removing characters can lead to data loss, especially if numbers are part of identifiers like SKUs or Product Codes. If you need to group similar values after cleaning, see Excel Formula to group similar values for further processing steps.

Frequently Asked Questions

Can I remove numbers while keeping spaces?

Yes, both the formula and VBA methods described above preserve spaces because their ASCII codes are outside the 48-57 range reserved for digits. The logic specifically targets numeric characters, leaving whitespace intact.

What if I want to remove letters instead of numbers?

You would invert the logic. In the formula, change NOT(isNum) to isNum. In VBA, change the condition to If charCode >= 48 And charCode <= 57 to keep only digits.

Does this work in Google Sheets?

The LET and SEQUENCE functions are supported in Google Sheets, so the formula approach works identically. However, the VBA code will not work; you would need to write a Google Apps Script equivalent.

How do I handle negative signs when removing numbers?

The current methods treat the minus sign (-) as a non-number character, so it will be preserved. If you want to remove negative signs along with numbers, you must add an additional check for the ASCII code 45 in your logic.

Is there a built-in Excel feature for this?

No, Excel does not have a native "Remove Numbers" button in the Ribbon. Power Query can achieve this using advanced M language code, but it is more complex for simple cell-level operations compared to the formulas provided here.