Explanation
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:
MID(B5,SEQUENCE(LEN(B5)),1)
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:
=MID(B5,{1;2;3;4;5;6;7;8;9},1)
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:
=COUNT({1;8;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})
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:
=COUNT(--MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))
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:
=COUNT(--MID(B5,{1;2;3;4;5;6;7;8;9},1))
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.
Related formulas
Split text string to character array
Cell contains number
Related functions
COUNT Function
The Excel COUNT function returns a count of values that are numbers. Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers. Empty cells and text values are ignored....
SEQUENCE Function
The Excel SEQUENCE function generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional, determined by rows and columns arguments.
MID Function
The Excel MID function extracts a given number of characters from the middle of a supplied text string based on the provided starting location. For example, =MID("apple",2,3) returns "ppl".
LEN Function
The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
INDIRECT Function
The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
ROW Function
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
