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.

• By using a formula (Excel 2021 and later)
• By using Kutools AI Aide (Intelligent & Efficient)
• By using an amazing tool (One click)
• By using VBA code

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

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.

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 Kutools for Excel 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.

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 Kutools for Excel 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.

Related articles