Count Number of Lines Within a Cell

[seguin85]

Hi,
Is there a way to count the number of lines within a cell? I found a formula and VBA to count the line breaks which is great, but I need something that will also count when a wrapped text goes to the next line.

It may be important to note that the cells are merged...

Thanks in advance!

 

[jasonb75]

Take a look here

Counting lines in a wrapped cell

Trying to count the number of lines inside a wrapped cell. For example, if a long sentence wraps to 5 lines inside a cell, the function or macro should return "5". The following thread seems to address a similar question, but I am wondering if a simpler solution exists...

www.mrexcel.com

 

[seguin85]

Take a look here

Counting lines in a wrapped cell

Trying to count the number of lines inside a wrapped cell. For example, if a long sentence wraps to 5 lines inside a cell, the function or macro should return "5". The following thread seems to address a similar question, but I am wondering if a simpler solution exists...

www.mrexcel.com

Thank you for looking into this for me, but I found this before and it didn't seem to work on my sheet for whatever reason. No matter what is in the cell it returns 1 line...

 

[jasonb75]

Both methods work for me. I assume that you're looking to count the lines in A1, or if not that you changed the code so that it looked at the correct cell?

 

[seguin85]

I believe the issue could be that it contains merged cells which will not allow the row height to auto format.

Edit: It works for other cells on the sheet, but not in the column I am looking at.

 

[jasonb75]

I get the same thing with merged cells, best not to use them.

 

[Rick Rothstein]

I found this before and it didn't seem to work on my sheet for whatever reason.

If you do not have merged cells (as per seguin85's guess above), then I think the problem might be that you resized the row height manually which would stop the method in that link from working. If that is the case, you can remove the manual resize this way... select the cell and make sure Wrap Text is not turned on, then double click the row separator line (in the row header... the gray vertical bar with the row numbers in it) that is aligned with the bottom of your cell. That will reset the row height to the standard height. Now turn on Wrap Text and try the code from the link again.

 

[seguin85]

I get the same thing with merged cells, best not to use them.

I agree, it isn't my sheet, but someone within the company asked me to do this. Anyways, what I am trying to get to it to auto format the row height in the merged cell based on line breaks and when line wrapping is in the same cell. I was trying to count the number of lines in a cell and multiply by 15.

 

[jasonb75]

but someone within the company asked me to do this.

I would go back and tell them that they have a greater chance of winning the lottery jackpot every week for a year without buying a ticket

With merged cells, (therory) at best you could compare character width to cell width to get a character count per line, then compare that to the number of characters in the cell to get the number of lines.
This would assume that each line contains the maximum number of characters, which will be highly unlikely. In addition to this, the character count per line would be variable depending on the font used, using Calibri, W is 3 times the width of I but using a mono-spaced font, both are equal. This would be inaccurate and unreliable at best, that is assuming that it is even possible to make it work at all.

 

[Rick Rothstein]

In addition to this, the character count per line would be variable depending on the font used, using Calibri, W is 3 times the width of I but using a mono-spaced font, both are equal.

Not to mention that, assuming the text in the cell is not from a formula, individual characters within the text can be resized differently from the remaining text in the cell.