# Is it possible to count lines of text in a cell (thus obtain a line sequence number)? [closed]

I need every line (including wordwrapped ones) to have its own sequential number. These are separate from cell numbers which only count as one digit when a cell is wordwrapped. Is this possible ?

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-27 12:16:23.510058

Sort by » oldest newest most voted

I hope the answer is "no" (except by some tricky programming) and I am confident so far.

If you can manage to have lines of text in your cells which are defined by inserted line breaks (Ctrl+Enter; ASCII 10), counting is posible now using some helpers - or by a single formula. if a SMALL maximum is given for the number of lines. You may use the result to control further "actions".

For a maximum of 3 lines in cell A4 the formula =IF(NOT(ISNUMBER(FIND(CHAR(10);A4)));1;IF(NOT(ISNUMBER(FIND(CHAR(10);A4;FIND(CHAR(10);A4)+1)));2;3)) should work.
(Editing 2016-02-27: Since @alex Kemp closed this thread today my attention was drawn to it again. Despite the closing I now want to document my advancements concerning Calc by giving a much better solution to the question in the sense I first interpreted it:
=1 + SUMPRODUCT(CODE(MID(A4;COLUMN(OFFSET($A$1;0;0;1;MAX(LEN(A4);1)));1))=10) [Lupp])
My advice for free: DO NOT RELY ON SUCH TRICKERY! If you need the "lines" as separate objects - LET THEM BE SEPARATE! Every item in its own cell! You may then COMPOSE some of these items to a multiline content of another cell easily.

As I am 70 I wont figure a youngster. Just: I think I got your intentions the first time exactly as you describe them now in more deail. I still think there aren't and shouldn't be means by formulae to get what you want. I also still think if this was possible it had the power of causing problems much more than it might help solve. Calculating numbers of lines is a ticklish issue. Just tried it again this way: A sheet on the monitor and the same sheet in print preview. It wasn't difficult to find an example where wrapping for the same cell was different (and the numbers of lines, too).

If you are capable of programming in some depth you may find ways to get the number of "displayed lines" (for YOUR monitor). Calc and its helpers (There is an OS and a lot more!) must know a way to insert the "virtual line breaks". I don't know which objects/instances are concerned with this. In case of printing it won't work without the printer driver. (I well know that it sometimes fails.)

I would prefer dissecting the cell content and recompose it as needed. This way I can get reliable line numbering.

[end EDIT]

more

@Lupp - Thanks for your comment. Seeing at the end your answer, I deleted mine. Seems that my interpretation of the question was not good enough. My interpretation was more in line with your "free advice".

( 2014-07-02 11:59:18 +0100 )edit

Hi Thanks for the suggestion. My request may not have been clear enough. I will try again ! I am using calc spreadsheet.To simplify things - In cell A1 there are two characters. In cell B1 there are several words. If there are more words than available space, wordwrap creates a second (or more) line/s in cell B1. Supposing there are 5 lines. When cell C1 is selected it enlarges to 5 lines (as well as all other 1cells). If I use the line numbering system it names the whole of C1 as number 1. Somehow I would like to be able to show numbers 1-5 (the number of lines) rather than just the cell number (1). As an 81yr old, my explanation might be like me - muddled at times! Incidentally I am using Macs 10.6.8 Thanks for any help you may offer. Maurice

( 2014-07-02 13:05:25 +0100 )edit

@mpkettle, in order to count each "line" it is necessary to explicitly define what a line is e.g., 80 characters. The original data can then be left as-is and a formula / regular expression applied to parse each cell and calculate the number of lines (and add this amount to a running total). This is essentially what @Lupp is suggesting above, but via a different method.

( 2014-07-02 13:31:10 +0100 )edit

Thanks @ lupp and @oweng - I believe you are both on the right paths. If I count the characters I guess I would need to use a constant width font. Between myself and my XLS fanatical daughter I am sure we can get this working, Might take some weeks but I will let you know the result.

( 2014-07-03 03:56:15 +0100 )edit