Skip to main content

Explore the new, free version of Kutools for Outlook today!

How to remove non-numeric characters from cells in Excel?

Author: Mandyzhou Last Modified: 2024-06-18

Excel users often encounter data that includes a mix of numbers and non-numeric characters, such as letters or symbols. Cleaning up this data can be essential for further analysis or reporting. In this guide, we'll explore four methods to remove non-numeric characters from cells in Excel, ensuring your data is clean and usable.

Remove non-numeric characters from cells in Excel

In this section, we will introduce four effective methods to remove non-numeric characters from Excel cells, tailored for different versions of Excel and user expertise levels.

Remove non-numeric characters from cells in Excel by using a formula

To remove non-numeric characters from cells in Excel, you can use a formula based on the TEXTJOIN function.

Step 1: Select a cell and apply the formula

In cell C2, apply the formula below, and press the Enter key to get the result.

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)+0,""))+0
The TEXTJOIN function is available in Excel 2019 and later. And the SEQUENCE function is available in Excel 2021 and later. If you want an easier way to achieve the goal in all Excel versions, refer to the By using Kutools AI Aide method or the By using an amazing tool method we will introduce next.
Step 2: Apply the formula to other cells and get all results

After calculating the initial result, drag the fill handle from the formula cell (C2 in this example) down to apply the formula to other cells, extending it to cell C8.

Formula explanation:
  1. LEN(A2): Finds the length of the string in A2.
  2. SEQUENCE(LEN(A2)): Creates a list of numbers from 1 to the length of the string. Each number corresponds to a character position in the string.
  3. MID(A2, SEQUENCE(LEN(A2)), 1): Extracts each character from A2 one at a time.
  4. +0: Tries to convert each character into a number. If a character isn't a number, it causes an error.
  5. IFERROR(..., ""): If converting a character causes an error (meaning it's not a number), it's replaced with an empty string.
  6. TEXTJOIN("", TRUE, ...): Combines all the characters back into a single string, skipping any empty strings (the non-numerics that were removed).
  7. +0 at the end: Converts the final string of numbers back into a numeric value.

Remove non-numeric characters from cells in Excel by using Kutools AI Aide

Kutools for Excel's AI Aide feature offers unparalleled convenience in cleaning Excel sheets by effortlessly removing non-numeric characters. With just a simple input from you, this powerful tool takes over, automatically analyzing and executing the required tasks without the need for complex formulas. This not only speeds up the process but also eliminates the potential for errors, significantly boosting your productivity and accuracy. The AI Aide turns what could be a tedious manual task into a seamless, efficient operation, making it an essential asset for any Excel user aiming to optimize their workflows.

After downloading and installing Kutools for Excel, click Kutools AI > AI Aide to open the Kutools AI Aide pane.

1. In the Kutools AI Aid pane, enter your request like the text below into the chat box, and click Send button or press Enter key to submit your query.

"Please remove non-numeric characters from range A2:A8 in Excel for me."

2. Once the tool has analyzed your input, click the Execute button. Kutools AI Aide will employ its AI technology to process your request and apply the results directly in Excel.

Tip: To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.

Remove non-numeric characters from cells in Excel by using an amazing tool

For efficiently cleaning data by removing non-numeric characters across a specified range, Kutools for Excel’s Remove Characters utility provides a one-click solution. This powerful tool simplifies what can often be a cumbersome process, allowing you to swiftly eliminate unwanted characters from your data, ensuring cleaner, more usable results instantly.

Note: To apply this Remove Characters feature, firstly, you should download and install Kutools for Excel.

1. After selecting the range from where you want to remove non-numeric characters, click Kutools > Text > Remove Characters.

2. In the Remove Characters dialog box, check the Non-numeric option, and click OK. Then the non-numeric characters are removed from the selection immediately.

Tip: To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.

Remove non-numeric characters from cells in Excel by using VBA code

To remove non-numeric characters from a range with VBA code, please do as follow:

Step 1: Open the VBA module editor and copy the code
  1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, then copy and paste the following code in the Module Window.
  3. VBA code: Remove all non-numeric characters 
    Sub RemoveNotNum()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[0-9]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub
Step 2: Execute the code
  1. After pasting this code, please press F5 key to run this code.
  2. In the popping up KutoolsforExcel dialog box, select a range from which you want to remove the non-numeric characters, then click OK.

Result

Then all non-numeric characters in selected range are removed.

If your data includes numbers with decimal points, you can employ the following VBA script: 
  1. Sub RemoveNotNum()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[0-9.]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub
  2. Result