How to Remove Numeric Characters from Cells in Excel?

Get FREE Advanced Excel Exercises with Solutions!

Today, in this article, we will demonstrate 5 suitable methods of how to remove numeric characters from cells in Excel. However, it is a very common problem, especially when you are dealing with raw data. Sometimes, 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. Hence, you will learn some of the easiest ones to complete the job.

overview of using the TEXTJOIN Function to Remove Numeric Characters from Cells

Here, we have used the TEXTJOIN function along with some other functions in order to remove numeric characters from cells in Excel. Read the rest of the part to learn the process in detail.


How to Remove Numeric Characters from Cells in Excel: 5 Easy Ways

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.

Sample Dataset


1. Apply TEXTJOIN Function to Remove Numeric Characters from Cells

You can use a formula based on The TEXTJOIN function to remove numeric characters from cells. 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. Hence, stay with us to learn!

Steps:

  • Firstly, in cell C4, apply the formula below.
=TEXTJOIN("",TRUE,IF(ISERR(MID(B4,ROW(INDIRECT("1:100")),1)+0),MID(B4,ROW(INDIRECT("1:100")),1),""))

Apply The TEXTJOIN Function to Remove Numeric Characters from Cells

Formula Breakdown:

  • Here, we used the MID function, followed by the ROW function, and the INDIRECT function to cast the letters in a text string.
  • Then, 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}
  • Additionally, 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.
  • However, 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.
  • After that, the final result goes into the TEXTJOIN function as text1. For the delimiter, we use an empty string (“”) and to ignore_empty we supply TRUE.
  • Lastly, TEXTJOIN then returns the result by connecting all non-empty values in the array.
  • Since this is an array formula, press CTRL+SHIFT+ENTER to get the result. If you are using Excel 365, just hit ENTER.
  • Finally, utilize the AutoFill tool for the entire column.

result of Remove Numeric Characters from Cells using AutoFill tool

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


2. Combination of TEXTJOIN and SEQUENCE Functions for Removing Numeric Characters

Additionally, you can insert the SEQUENCE function to remove numeric characters from cells. Hence, follow the steps below.

Steps:

  • Initially, 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,
=TEXTJOIN("",TRUE,IF(ISERR(MID(B4,SEQUENCE(LEN(B4)),1)+0),MID(B4,SEQUENCE(LEN(B4)),1),""))

cobination of TEXTJOIN and SEQUENCE Functions to Remove Numeric Characters from Cells

Formula Breakdown:

  • 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.
  • Next, press ENTER.
  • Finally, apply the same formula to the rest of the cells to achieve the final result with the AutoFill tool.

Remove Numeric Characters final result


3. Erase Numeric Characters from Cells Through LET Function

Furthermore, you can use the LET function to remove numeric characters from cells. Hence, follow the steps below.

Steps:

  • Firstly, select cell C4 and insert the following formula.
=LET(ARRAY,SEQUENCE(LEN(B4)),TEXTJOIN("",TRUE,IF(ISERR(MID(B4,ARRAY,1)+0),MID(B4,ARRAY,1),"")))

Use LET Function in Excel

Note:

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

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

LET Function to remove numeric characters result


4. Use the TRIM Function to Delete 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 the steps below to learn this method.

Use of TRIM Function dataset

Steps:

  • First, in cell C4, apply the formula. The formula is,
=TRIM(TEXTJOIN("",TRUE,IF(ISERR(MID(B4,ROW(INDIRECT("1:100")),1)+0),MID(B4,ROW(INDIRECT("1:100")),1),"")))

TRIM Function to remove numeric characters from cells

Note:

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.

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

TRIM Function output


5. Run 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.

Steps:

  • First, we will open our VBA macro window. To do that, press Alt+11.
  • Then, a new window is opened.
  • Now, click Insert and select Module to open a new module.

insert module in vba window

  • Thereafter, in the newly opened module, Insert the VBA code to remove numeric characters. However, 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

VBA Code to Remove Numeric Characters from Cells

  • Now, run the code and select your range.
  • Then, click OK to conclude the operation.

vba dialog box in excel

  • Lastly, we have successfully removed those numeric characters from our cells.

Remove Numeric Characters result with vba in excel

Read More: How to Remove Characters from String Using VBA in Excel


Things to Remember

  • Initially, 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.
  • Then, the SEQUENCE function is only available for Excel 365 version. Moreover, you cannot use this function if you are using older versions of Excel.
  • Afterward, the TEXTJOIN function is active for the users of Excel 2019. So the users of the old versions of Excel cannot use this function.

Download Practice Workbook

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


Conclusion

We have gone through 5 suitable methods of how 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 Excel tasks!


Related Articles

<< Go Back To Remove Specific Characters in Excel | Excel Remove Characters | Data Cleaning in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo