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.
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.
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:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B4,ROW(INDIRECT("1:100")),1)+0),MID(B4,ROW(INDIRECT("1:100")),1),""))
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.
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),""))
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.
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),"")))
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.
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.
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),"")))
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.
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.
- 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
- Now, run the code and select your range.
- Then, click OK to conclude the operation.
- Lastly, we have successfully removed those numeric characters from our cells.
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
- How to Remove Characters from Left in Excel
- How to Remove Characters from Left and Right in Excel
- How to Remove Special Characters in Excel
- How to Remove Characters After a Specific Character in Excel
- Excel Remove Characters From Right
- How to Remove Specific Characters in Excel
<< Go Back To Remove Specific Characters in Excel | Excel Remove Characters | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
