Abrar
In our day-to-day use of Excel, we can come across datasets where we might have to know how to remove non-numeric characters from cells in Excel. In this article, we will be taking a look at the different approaches in which we can remove such non-numeric characters and keep only numbers as output values.
📁 Download Excel File
Download the practice workbook below.
What Are Non-Numeric Characters?
Non Numeric characters on our keyboard mean the characters, symbols, and signs that are present other than numerical values. So, anything other than numbers can be termed as non-numeric characters.
Learn to Remove Non-Numeric Characters from Cells in Excel with These 3 Approaches
To demonstrate things easily, we will consider a dataset where we have different text strings with different characters including alphabets, symbols, and signs along with numbers. We will use our sample data table below to remove non-numeric characters from cells and only keep numbers as outputs in Excel by the following methods. The dataset is shown below:
1. Using TEXTJOIN and SEQUENCE Functions
We can use the functions TEXTJOIN and SEQUENCE to remove non-numeric characters from cells. This approach uses the combination of TEXTJOIN, TRUE, IFERROR, MID, LEN, and SEQUENCE functions within a formula to remove non-numeric characters. The steps in this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we select cell C6.
- After that, we write the following formula in the formula bar.
=TEXTJOIN("", TRUE, IFERROR(MID(B6, SEQUENCE(LEN(B6)), 1) *1, ""))🔨 Formula Breakdown
TEXTJOIN(“”, TRUE, IFERROR(MID(B6, SEQUENCE(LEN(B6)), 1) *1, “”))
👉 We use the LEN Function to return the number of characters in cell B6.
👉 SEQUENCE Function gives an output of an array of sequential values where the first argument here is the number of rows to return as a value.
👉 MID Function gives an output of the extracted value of characters. The first argument in this function, that is B6 indicates the text in B6 where we will extract the value from. The second argument is the location of the first character to extract and the third argument is the number of characters we need to extract, that is 1.
👉 There are two arguments in the IFERROR Function. The first argument specifies the value to check for an error and the second argument specifies the value to return if an error is present.
👉 TRUE Function returns the boolean value TRUE.
👉 TEXTJOIN Function joins the text values with a delimiter where the delimiter is the separator between texts.
- After that, we press Enter. We will see that the final result in cell C6 gets rid of all the characters except numbers from the text string.
- Then we hover our cursor to the bottom right of cell C6 and drag the Fill Handle up to cell C13.
- Thus, we will see that the formula will copy itself across all the cells and we will get our output by removing non-numeric characters. The final output will look like the following image.
📕 Read More: 4 Ways to Remove Last 3 Characters in Excel
2. Combining TEXTJOIN and INDIRECT Functions
We can use the TEXTJOIN function along with the INDIRECT function in a similar way to the previous method to remove the non-numeric characters within the cells in Excel. The basic steps for this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- First, we select cell C6.
- Then, we write the following formula in the formula bar.
=TEXTJOIN("",TRUE,IFERROR(MID(B6,ROW(INDIRECT("1:100")),1)+0,""))🔨 Formula Breakdown
TEXTJOIN(“”,TRUE,IFERROR(MID(B6,ROW(INDIRECT(“1:100″)),1)+0,””))
👉 We can use the LEN Function to return the number of characters in cell B6.
👉 We use the INDIRECT Function to create a reference from text.
👉 By using the MID Function we can return the extracted value of characters. This function’s first argument, that is B6 indicates the text in B6 from where we will extract the value. The second argument gives us the location of the first character to extract and the third argument is the number of characters we need to extract, that is 1.
👉 IFERROR Function has two arguments, The first argument determines the value to check for an error and the second argument specifies the value to return if an error is present.
👉 The TRUE Function returns the boolean value TRUE.
👉 A delimiter is the separator between texts. TEXTJOIN Function joins the text values with a delimiter.
- Next, we press Enter. We will see that the final result in cell C6 eliminates all the characters except numbers from the text string.
- After that, we hover our cursor to the bottom right of cell C6 and drag the Fill Handle up to cell C13.
- Thus, we will see that the formula will copy itself across all the cells and we will get our output by removing non-numeric characters. The final output will look like the following image.
📕 Read More: 5 Ways to Remove Last Character from String in Excel
3. Executing VBA Code
Apart from the two approaches mentioned, we can also insert VBA Code and remove non-numeric characters from cells. The steps for this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At the beginning, we go to the Developer tab and then select Visual Basic.
- As a result, a new Visual Basic window will appear.
- Then we go to the Insert tab and create a Module by selecting Module.
- In the Module tab on the right, we write the following VBA Code.
Function Remove_Non_Numeric_Character(st As String)
Dim STR As String
STR = ""
For i = 1 To Len(st)
If True = IsNumeric(Mid(st, i, 1)) Then
STR = STR & Mid(st, i, 1)
End If
Next i
Remove_Non_Numeric_Character = STR
End Function- After writing the code, we press the Run button.
- Now, we move on to cell C5. After that, we write the following formula in the formula bar.
=Remove_Non_Numeric_Character(B6)
- We will see that the non-numeric characters have been removed. Also, the output consists of only numbers in cell C5.
- Now, we drag the Fill Handle button of cell C5 and drag it up to cell C13.
- The resultant output will look like the following image.
📄 Important Notes
🖊️ If we cannot find the Developer tab, we need to activate it. We can do so by going to Customized Quick Access Toolbar and then clicking More Commands. After that, we select Developer from Customize Ribbon and then press OK.
🖊️ The approach with VBA Code is beneficial for large volumes of data. It is more efficient than the other two approaches.
🖊️ We can alternatively use the shortcut Alt+F11 to open the Visual Basic Editor..
📝 Takeaways from This Article
You have taken the following summed-up inputs from the article:
📌 We can use TEXTJOIN Function combined with the SEQUENCE function and alternately with the INDIRECT function to remove non-numeric characters from cells in Excel.
📌 Additionally, using VBA Code is the most accepted form by which we can remove non-alphanumeric characters very easily.
Conclusion
In conclusion, I hope that this article has provided you with insights into how we remove non-numeric characters from cells in Excel. If you want to know more about Excel, please visit our site ExcelDen and if you have any queries regarding this topic, feel free to let us know down below in the comment section.
