How to Remove Numeric Characters from Cells in Excel (5 Methods)

Today in this article, we will demonstrate how to remove numeric characters from cells in excel. It is a very common problem especially when you are dealing with raw data. Numbers and texts are mixed in the cells and sometimes we need to separate those two different values. There are many formulas to remove numeric characters from cells in excel. We will learn some of the easiest ones to complete our job.

Download this practice book to exercise the task while you are reading this article.

5 Easy Methods to Remove Numeric Characters from Cells in Excel

Imagine a situation when you receive a raw dataset to analyze and you see that numbers are mixed with text in one column. To work conveniently, you need to separate them from one another. Here in this section, we will learn five suitable methods to do that job.

1. Apply The TEXTJOIN Function to Remove Numeric Characters from Cells

You can use a formula based on The TEXTJOIN function to remove numeric characters from cells. For that, first, you have to create a new column adjacent to your existing column where you will extract the result. We will discuss the whole operation step by step. Stay with us to learn!

Step 1:

• In cell C4, apply the formula based on the TEXTJOIN The Formula looks like this,

• Here we used The MID function, followed by The ROW function, and The INDIRECT function to cast the letters in a text string.
• The MID function is applied to extract the text in B4 one text at a time.

• Where 100 represents the maximum number to look at. This MID function starts an array containing 100 numbers like this,

{1,2,3,4,5,6,7,8….99,100}

• We use this array as a start_num argument and for num_chars, we use 1.
• So now numeric text values like 1,2,3 will convert without errors and the non-numeric numbers won’t be able to do that. As a result, they will return a #Value We use The IF function and The ISERR function to catch those errors and bring those values into the existing array.
• If we don’t get an error, then it’s a number and for that, we insert an empty string (“”) into the array in place of the number.
• The final result goes into the TEXTJOIN function as the text1. For the delimiter, we use an empty string (“”) and to ignore_empty we supply TRUE.
• TEXTJOIN then returns the result by connecting all non-empty values in the array.

Step 2:

• Since this is an array formula, press CTRL+SHIFT+ENTER to get the result. If you are using Excel 365, just hit ENTER.

Step 3:

• Hover over your cursor to the bottom right corner of the cell and then double click to apply the same formula to all the cells in the dataset.

Read More: How to Remove Non-numeric Characters from Cells in Excel

2. Insert The SEQUENCE Function Remove Numeric Characters from Cells

Step 1:

• You can use The SEQUENCE function-based formula if you are using Excel 365. This formula is shorter than the one we discussed above. The formula is,

• The SEQUENCE function can replace the ROW and the INDIRECT functions and serve the same purpose.
• In this formula, we used the SEQUENCE and the LEN function to build an array of the correct length in one step.

Step 2:

• Get the result by pressing ENTER.

• Apply the same formula to the rest of the cells to achieve the final result.

Read More: How to Remove Characters in Excel (6 Methods)

3. Use The LET Function to Remove Numeric Characters from Cells

Step 1:

• We can further precise this formula with The LET function. For that, the formula is,

• Here using the LET function without creating an array twice, we can define the array as a variable and create it just once.

Step 2:

• Press ENTER and apply the same formula to all cells required. The final result is here.

Related Content: How to Remove Characters from Left in Excel (6 Ways)

Similar Readings:

• How to Remove Hidden Double Quotes in Excel (6 Easy Ways)
• How to Remove Single Quotes in Excel (6 Ways)
• Remove Last Character from String in Excel with VBA (2 Easy Ways)
• How to Remove Non-Printable Characters in Excel (4 Easy Ways)
• Remove First Character from String in Excel (6 Quick Ways)

4. Use the TRIM Function to Remove Numeric Characters from Cells

Sometimes you may find extra space between your raw data mixed with texts and numbers. The TRIM function can help you out in this situation by removing those extra spaces. Follow these steps below to learn this method.

Step 1:

• In cell C4, apply the formula. The formula is,

Here we didn’t change anything but added the TRIM function in front of the TEXTJOIN formula we discussed in method 1. You can also use the SEQUENCE or the LET based formula

Step 2:

• Hit ENTER to get the result. Now we can see that our extra space problem is solved.

Related Content: How to Remove Spaces in Excel: With Formula, VBA & Power Query

5. Run a VBA Code to Remove Numeric Characters from Cells

You can use the VBA macro code to remove numeric characters from cells in excel. We will demonstrate this method below.

Step 1:

• First, we will open our VBA macro window. To do that, press Alt+11.

• A new window is opened. Now click Insert and select Module to open a new module.

Step 2:

• In the newly opened module, Insert the VBA code to remove numeric characters. We have provided the code for you. You can just copy this code and use it in your worksheet. The code is,

Sub RemoveNumber()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "VBA code to remove number"
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 = ""
            Else
            xStr = xTemp
            End If
            xOut = xOut & xStr
            Next i
            Rng.Value = xOut
Next
End Sub

Step 3:

• Now run the code. Select your range and click ok to conclude the operation.

• We have successfully removed those numeric characters from our cells.

Read More: How to Remove the First Character from a String in Excel with VBA

Things to Remember

👉 If you are using the array formulas, press CTRL+SHIFT+ENTER simultaneously to get the result. When you are using Excel 365, then just press ENTER.

👉 The SEQUENCE  function is only available for Excel 365 version. You cannot use this function if you are using older versions of Excel

👉 The TEXTJOIN function is active for the users of Excel 2019. So the users of the old versions of Excel cannot use this function.

Conclusion

We have gone through five different approaches to remove numeric characters from cells in excel. You are most welcome to comment if you have any questions or queries. You can also check out our other articles related to the Excel tasks!

Related Articles

• How to Remove Apostrophe in Excel (5 Easy Methods)
• How to Remove Parentheses in Excel (4 Easy Ways)
• Remove Characters after a Specific Character in Excel (4 Tricks)
• How to Remove Semicolon in Excel (4 Methods)
• How to Remove Non-Alphanumeric Characters in Excel (2 Methods)