Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW function with the INDIRECT function here: This snippet spins up an array containing 100 numbers like this: Note: 100 represents the maximum characters to process; adjust to suit your data. This array goes into the MID function as the start_num argument. For num_chars, we use 1. With these inputs, the MID function returns an array like this: Note: extra items in the array removed for readability. To this array, we add zero. This is a simple trick that forces Excel to try and coerce a value to a number. Numeric text values like “1”,“2”,“3”,“4” etc. are converted, while non-numeric values fail and throw a #VALUE error. We use the IFERROR function to catch these errors and return an empty string (""), while numeric values pass through into an output array. The result is an array that contains just numbers and empty strings: Finally, this array result is delivered to the TEXTJOIN function as the text1 argument. For delimiter, we use an empty string ("") and for ignore_empty we supply TRUE, since we only want to work with the actual values that remain. TEXTJOIN then concatenates all non-empty values in the array and returns a final result. Note: TEXTJOIN will return the numbers as text, for example “100,“500”, etc. If you want a true numeric result, you can add zero (+0), or wrap the entire formula in the VALUE function.
Strip numeric characters
To strip numeric characters, and preserve only text characters, you can use a formula like this: This formula is explained here.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
