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?”

    5 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.

    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.

    MMMY
    5 years ago

    Thanks Mr. Rosenzweig for your quick reply
    I Hope you find the time to post a tutorial regarding this problem
    👍🏾

    MMMY
    5 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

    5 years ago

    MMMY: Here is an example you can download: https://www.dropbox.com/s/gxrt0fxftyfwb9c/CountLines.numbers?dl=0

    MMMY
    5 years ago

    Thanks Mr. Rosenweig
    It worked
    I truly appreciate your assistance

    MMMY
    5 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

    5 years ago

    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."

    MMMY
    5 years ago

    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

    5 years ago

    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.

Comments Closed.