Number of Lines in a cell

James006

James006

Well-known Member
Joined
Apr 4, 2009
Messages
4,750
Office Version
  1. 2016
Platform
  1. Windows
Hi everybody,

My question deals with cells formatted with wrap text ...
As long as the combination Alt Enter is used, a counter can work out the number of lines ...
But, how to count the number of lines displayed in a "wrap-text cell" ...
since this number of lines is a direct function of row height and column width ...???

Thanks in advance for your comments

P.S. If you know, my research is vain :( , please let me know ...
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Hi again,

It would seem that MVP assistance is required for this headache ...!!!

Cheers
 
Upvote 0
You could probably use VBA to calculate this, but it would take quite some work if things were changed from the normal.

You could possibly take the row height and divide it by the "normal" row height for a single line cell.

But if people change fonts or font sizes or just change the height without the cell having anything in it, you would not get the correct answer. It would also only provide the largest value, so everything in that row would then return the same number of lines regardless of how many lines were actually in the cell.

There are too many variables I would think for this to be possible.

The alt+Enter is easy to count as such:
Excel Workbook
FG
1921
20
21This Is RobThis s
Sheet1
Cell Formulas
RangeFormula
F19=LEN(F21)-LEN(SUBSTITUTE(F21,CHAR(10),""))
G19=LEN(G21)-LEN(SUBSTITUTE(G21,CHAR(10),""))


Sorry it was proabbly not the answer you were looking for.
 
Upvote 0
I have the same query James006 - did you find an answer?
my cells have no Char(10). the text is wrapped and I'd like to automate how many lines in a cell
thanks
james
 
Upvote 0
JamesM London said:
I have the same query James006 - did you find an answer?
my cells have no Char(10). the text is wrapped and I'd like to automate how many lines in a cell
thanks
james
Click to expand...

Sadly no ... had to find a totally different path ...

Cheers
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Customized text wrapping to new row (not within cell)
Replies
8
Views
191
rlv01
R
K
  • Solved
Help selecting variable range for cut and paste
2 3
Replies
27
Views
1K
OaklandJim
O
L
  • Question
Numbers as text
Replies
4
Views
438
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Solved
Counting the number of User Logins per Each Month
Replies
5
Views
129
RobP
R
K
  • Question
Lottery Combinations Filter
Replies
2
Views
538
Fluff
Fluff

Forum statistics

Threads
1,214,864
Messages
6,121,986
Members
449,060
Latest member
mtsheetz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top