Count number of non-empty lines in a multiline cell

[ch1zra]

Hello everyone,

I am having trouble with one of the sheets at work that is created and maintained in quite a bad manner.
It contains multiline cells but not all lines are containing text, some are just plan char(10).
What I need is a way to count number of lines that are non-empty.
I can get number of total lines using

=(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))+1

but I am stuck on how to continue...

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ch1zra]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Well noted. I have updated it. 2021 & 365 for enterprise.

 

[Fluff]

Thanks for that.
Which version does this need to work in?

 

[ch1zra]

It needs to work in 365

 

[Fluff]

Ok, how about

=ROWS(TEXTSPLIT(A1,,CHAR(10),1))

 

[ch1zra]

Ok, how about

=ROWS(TEXTSPLIT(A1,,CHAR(10),1))

Works like a charm, thanks a lot.
For pure curiosity, how would it be handled in 2021?

 

[Fluff]

Like

=LET(a,TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","|"),CHAR(10)," ")),LEN(a)-LEN(SUBSTITUTE(a," ",""))+1)

 

[ch1zra]

Like

=LET(a,TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","|"),CHAR(10)," ")),LEN(a)-LEN(SUBSTITUTE(a," ",""))+1)

Hm, this one is returning 8, but there are 9 blank lines.

 

[Fluff]

Not sure why, it should return the same value.
How about

=ROWS(FILTERXML("<k><m>"&SUBSTITUTE(A1,CHAR(10),"0</m><m>")&"</m></k>","//m[string-length()>1]"))