Forum Discussion
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 could also look like this:
1. text
2.
3.
4.
5.
On the sheet there are hundreds of multi-line cells that each contain the points 1-5 and I need a way to count how many of the 1-5 points for each cell have text after them. Does anyone know of a way to do this please?
Many thanks for your help.
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
4 Replies
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")))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
