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
MacMost Q&A Forum • View All Forum Questions • Ask a Question
How Do I Count Number Of Lines (Line Breaks) In a Cell In Numbers?
Comments: 9 Responses to “How Do I Count Number Of Lines (Line Breaks) In a Cell In Numbers?”
Comments Closed.
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.
Instead just start with this formula:
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.
Thanks Mr. Rosenzweig for your quick reply
I Hope you find the time to post a tutorial regarding this problem
👍🏾
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
MMMY: Here is an example you can download: https://www.dropbox.com/s/gxrt0fxftyfwb9c/CountLines.numbers?dl=0
Thanks Mr. Rosenweig
It worked
I truly appreciate your assistance
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
MMMY: Right. See my earlier comment about this:
"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."
Thank you very much Mr. Rosenweig.
I'm creating a form using Numbers for a friend and this formula being temperamental, not to mention in Numbers you can't specify certain cells to be locked in order to prevent users from changing them. My other alternative is to have him reprioritize his requirements.
Once again thank you so very much for your assistance
MMMY: One solution to remove the problem is to create a 1x1 table somewhere else in the sheet. Put a single return character in the cell (Use Option+Return to type it). Then refer to $A$1 in that sheet instead of the quotes around the pasted return character. Then you can lock that 1x1 table so it can't be altered.