< Go Back

Strip non-numeric characters

Many times, we have a text string with text and numbers. We can easily separate the text or words and the numeric values from the string using the TEXTJOIN function in a custom formula.  TEXTJOIN is an inbuilt function of excel and can be used to strip non-numeric characters from a text string.

TEXTJOIN Function

The TEXTJOIN function is used to concatenate or join two given strings. We’ll use the function with MID function to remove non-numeric characters from a string.

In this case, we use the generic formula:

{=TEXTJOIN(“”,TRUE,IFERROR(MID(A1,ROW(INDIRECT(“1:100”)),1)+0,””))}

The formula starts with curly brackets and ends with the same. Without these, the formula won’t work.

Example

In the figure, column B contains text string with non-numeric and numeric characters while we use column C to display the numeric characters only.

Figure 1: Entering text with the number

To remove the non-numeric characters from the text string, we apply the formula as shown in C5:

 {=TEXTJOIN(“”,TRUE,IFERROR(MID(A1,ROW(INDIRECT(“1:100”)),1)+0,””))}

Figure 2: Illustration of TEXTJOIN to strip non-numeric characters

Here, MID formula will remove the text from the column B5.

ROW(INDIRECT(“1:100”)) formula function is array spinning where the array and MID function together converts text into a number. The numeric values are easily converted while the non-numeric values fail in the process and an error occurs.

Through INFERROR function, the errors are caught and it results in numbers only with empty strings. . For example:

{“0”;”8”;””;””;….}

As soon as the result is displayed, through TEXTJOIN function, the empty values get linked and the final result gets displayed as shown.

Figure 3: Result of stripping non-numeric characters using TEXTJOIN

In this manner, the non-numeric text will be stripped out of the text. Through this formula, it will become hassle-free to remove the non-numeric text.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar