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.
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:
The formula starts with curly brackets and ends with the same. Without these, the formula won’t work.
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:
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:
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.