Counting Line Feeds Hard or Soft

  • Thread starter Thread starter Mark Hunter
  • Start date Start date
M

Mark Hunter

I am storing word wraped text in a cell and want to conrol
the ultimate cell height based on the number of carriage
returns / line feeds in the text hard or soft returns. Is
there a way to count them?

In the alternative I want to control row height based on
specific cells in the row not all cells. Is there a way to
do this?

Thanks,
 
Hi
one way to count the number of lines using worksheet function:
=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1

VBA solution:
Function count_lines(rng As Range)
If rng.Cells.count > 1 Then Exit Function
count_lines = Len(rng.Value) - Len(Replace(rng.Value, Chr(10), "")) + 1

End Function
 
Hi Mark,

You could do this in Excel with this formula:

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))

If you want to do it in VBA, you could do this:

Sub test()
With Range("A1")
MsgBox Len(.Value) - Len(Replace$(.Value, _
vbLf, vbNullString))
End With
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Is there a way to count lines that are word wrapped to a
new line but do not have a hard return?
 
Hi Mark
AFAIK not possible. This would require:
- counting the characters
- getting the current cell width
- getting the font type and size
- somehow calculation the numbers of characters per line

ut maybe someone here has a beter idea :-)
 
Back
Top