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

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
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
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
This comment was minimized by the moderator on the site
HI Thanks for the coding, i am getting the syntax error highlighted on For i=1 To Len(Rng.Value) and the debug Sub RemoveNotNum() is highlighted in yellow.. kindly advice.
This comment was minimized by the moderator on the site
The VBA works great. I modified it to always work on a column, as shown:

For Each Cell In Range("B2:B" & ActiveSheet.UsedRange.Rows.Count)

xOut=""

For i=1 To Len(Cell.Value)

xTemp=Mid(Cell.Value, i, 1)

If xTemp Like "[0-9]" Then

xStr=xTemp

Else

xStr=""

End If

xOut=xOut & xStr

Next i

Cell.Value=xOut

Next

I also used this to sort a column by the last digit, changing the last part to Cell.Value=Right(xOut, 1) then sorting it numerically.
This comment was minimized by the moderator on the site
Send me it on my email id
This comment was minimized by the moderator on the site
Hi, I want to use this macro but want to pre-define a range. Can you please let me know how to pre-define the range. Thanks, Samit
There are no comments posted here yet