To count numbers that appear in a text string, you can use a formula based on the COUNT function, with help from the MID, SEQUENCE, and LEN functions. In the example shown, the formula in cell D5 is:


The result in D5 is 2, since there are two numbers in B5. 

Generic formula



In this example, the goal is to count numbers that appear in column B. The COUNT function is designed to only count numeric values, but because all values in the range B5:B15 are text, COUNT will return zero. One approach is to split the characters in each text value into an array, then use the COUNT function to count numbers in the result. This approach is described below.

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:

=COUNT(--{"1";"8";" ";"a";"p";"p";"l";"e";"s"})

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 Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.