Ask Your Question

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

asked 2014-07-02 05:31:50 +0100

mpkettle gravatar image

updated 2014-07-02 13:35:17 +0100

oweng gravatar image

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted

answered 2014-07-02 10:24:12 +0100

Lupp gravatar image

updated 2016-02-27 15:24:25 +0100

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.

[begin EDIT] (Answering to the comment added by @mpkettle)

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]

edit flag offensive delete link 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".

ROSt52 gravatar imageROSt52 ( 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

mpkettle gravatar imagempkettle ( 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.

oweng gravatar imageoweng ( 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.

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

Question Tools

1 follower


Asked: 2014-07-02 05:31:50 +0100

Seen: 1,639 times

Last updated: Feb 27 '16