MacMost Q&A Forum • View All Forum QuestionsAsk a Question

# How Do I Count Number Of Lines (Line Breaks) In a Cell In Numbers?

I’m using Numbers Ver. version 6.2.1 (6529)
In MS Excel, I can use this nested formula to get the answer:
=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),””))+1
If I have 1,2,3,4,5, or more lines in one cell, example:
RB801011806SGCCB344
RF723117908SG221
RF723197265SG345
RB800038484SG23
2019081216311279133
The answer will be 5 in this case.
I know if I use LEN formula by itself for this case, I’ll get total number of characters in the cell which is 85 for this example
How do I create a nested formula in Numbers to solve this problem
Thank you very much
—–
MMMY

### Comments: 9 Responses to “How Do I Count Number Of Lines (Line Breaks) In a Cell In Numbers?”

3 years ago

You can do pretty much the same thing. The only difference is that the CHAR function in Numbers doesn't accept values less than 32 for some reason. But you can just skip using the CHAR function entirely.

LEN(A2)−LEN(SUBSTITUTE(A2,"X",""))
Then select a return character by editing the big text cell (A2) and put the text cursor at the end of the first line. Then hold the Shift key down and press the right arrow to select the return character between the two lines. Copy. Then go back to editing the formula, select the X and replace it with the return by pasting over it.

It is very temperamental, as it works if you paste the return character and immediately press Return to exit editing formula. But if you edit the formula again, it doesn't work. You have to paste the return character over that spot again.

MMMY
3 years ago

I Hope you find the time to post a tutorial regarding this problem
👍🏾

MMMY
3 years ago

Dear Mr. Rosenweig,
I keep getting this error message "The operator “-” expects a number, date, or duration, but cell D3 contains a string."
Is there a chance you can create a nested formula using the given example to give the required result of 5 lines & posted it so I can figure out my mistake.
Thank you very much

3 years ago

MMMY
3 years ago

Thanks Mr. Rosenweig
It worked

MMMY
3 years ago

Dear Mr. Rosenweig,
Initially, it worked fine however, now it returns 0 as a result. Even your example returns 0. I wonder if it has to do with cell formatting or other reason. Here're the 2 formulas copied exactly:
LEN(A2)−LEN(SUBSTITUTE(A2,"
","",))
LEN(D3)−LEN(SUBSTITUTE(D3,"","",))
Thank you very much

3 years ago