Forum Discussion

sjaybee58's avatar
sjaybee58
Copper Contributor
Jun 11, 2025
Solved

How to count text from a multi-line cell

Hi, I have an extract from a system that when opened in Excel looks like this in one multi-line cell: text text text text text Not all of the points 1-5 will contain text after them e.g. it ...
excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Jun 11, 2025

    To count the number of lines in a single cell you can use something like:

    =SUM(--(LEN(TRIM(TEXTSPLIT(A1,{"1."," 2."," 3."," 4."," 5."},,1)))>0))

    or

    =SUM(--(LEN(TEXTAFTER(TRIM(TEXTSPLIT(A1,CHAR(10),,1)),"."))>0))

    to then map that across a range you can use:

    =LET(r,A1:A6,
     l,LAMBDA(c,SUM(--(LEN(TEXTAFTER(TRIM(TEXTSPLIT(c,CHAR(10),,1)),"."))>0))),
     SUM(MAP(r,l)))

    so on line 1 is the range to check

    line 2 creates the lambda function based on the 2nd formula above

    line 3 maps and sums that lambda across the whole range

m_tarler's avatar
m_tarler
Bronze Contributor
Jun 11, 2025

To count the number of lines in a single cell you can use something like:

=SUM(--(LEN(TRIM(TEXTSPLIT(A1,{"1."," 2."," 3."," 4."," 5."},,1)))>0))

or

=SUM(--(LEN(TEXTAFTER(TRIM(TEXTSPLIT(A1,CHAR(10),,1)),"."))>0))

to then map that across a range you can use:

=LET(r,A1:A6,
     l,LAMBDA(c,SUM(--(LEN(TEXTAFTER(TRIM(TEXTSPLIT(c,CHAR(10),,1)),"."))>0))),
     SUM(MAP(r,l)))

so on line 1 is the range to check

line 2 creates the lambda function based on the 2nd formula above

line 3 maps and sums that lambda across the whole range

Resources