Forum Discussion
sjaybee58
Jun 11, 2025Copper Contributor
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 ...
- 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
ziadahm
Jun 11, 2025Copper Contributor
Here's another solution using REGEXREPLACE
=LEN(REGEXREPLACE(A1,"\d+\. *(\S).*|.|\n","$1"))To get the total, you can use:
=SUM(LEN(REGEXREPLACE(A1:A2,"\d+\. *(\S).*|.|\n","$1")))
- sjaybee58Jun 13, 2025Copper Contributor
Thank you ziadahm​