The result in D5 is 2, since there are two numbers in B5.
Create the array
The first step is to split the text string into an array of characters. This is done with MID, LEN, and SEQUENCE like this: In a nutshell, the LEN function returns the length of the text in B5, which is 9, to the SEQUENCE function as the rows argument. SEQUENCE then generates an a numeric array like {1;2;3;4;5;6;7;8;9}, which is returned to the MID function as the start_num argument: The MID function then extracts each of the 9 characters and returns an array like {“1”;“8”;" “;“a”;“p”;“p”;“l”;“e”;“s”}. Read a more detailed explanation here.
Count numbers
Back in the original formula, we use a double-negative (–) operation to force Excel to try and convert each character to a number: Where this operation succeeds, we get a numeric value. Where it fails, we get a #VALUE! error: The COUNT function then counts the numbers in the array and returns a final result of 2. As the formula is copied down column D, it returns a count of the numbers in each text string in column B.
Legacy Excel
In Legacy Excel, which lacks dynamic arrays and the SEQUENCE function, it is more challenging to split a text string to an array of characters. One workaround is to use the ROW and INDIRECT function like this: The INDIRECT function is a way of creating a valid Excel reference with text. The ROW function then evaluates the text and returns a reference. The reference in this case is 1:9 (rows 1 through 9), and the ROW function then returns an array of corresponding row numbers. The resulting array is the same as with the SEQUENCE function above: In the end, this formula returns the same result as above, 2. Note this is an array formula and must be entered with control + shift + enter in older versions of Excel.
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.
