Explanation
First, the LEN function counts total characters in the cell B5.
Next SUBSTITUTE removes all “line returns” from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of a second LEN, which counts characters without carriage returns.
The second count is subtracted from the first, and 1 is added to the final result, since the number of lines is the number of returns + 1.
Dealing with empty cells
The formula in the example shown will return 1 even if a cell is empty. If you need to guard against this problem, you can wrap the formula in IF statement like so:
=IF(ISBLANK(B5),0,LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),""))+1)
Mac version
=LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(13),""))+1
In Excel 365 , both Win and Mac versions of Excel use CHAR(10) as a line break.
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.
