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.

## Leave a Comment