Excel 2003 get text after wraptext problem

[Harry]

Hi all


I have a problem that is, to get the texts row by row and count the text line in one cell that wrapped in Excel 2003. After google
search, in the previous excel, I can find and count "char(10)" to determine the line break in text string, but in Excel 2003, no any
char(10) exists, even char(13) after wrapped (unless I type ALT-ENTER manually). Can anyone tell me how to do it?


Thanks for help

 

[Dave Peterson]

I don't know of any good way to count lines in a cell if the text wraps because
of columnwidth.

But you could use something like this to count the number of alt-enter's in a
cell:

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

if you're searching for multiple characters, you'd divide by the length of the
string:
=(LEN(A1)-LEN(SUBSTITUTE(lower(A1),"abc","")))/LEN("abc")
(substitute is case sensitive and this would count abc, ABC, AbC, ...

So if I wanted to count lines in a cell that are separated by alt-enters, I'd
use:
=(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))) + (A1<>"")

The +(A1<>"")
will handle the case where there are no alt-enters and check for an empty cell
at the same time.

==========
You posted this in the .programming newsgroup. Did you want a VBA solution?

Option Explicit
Sub testme()

Dim myCell As Range
Dim myLen As Long

Set myCell = Worksheets("sheet1").Range("A1")

With myCell
myLen = (Len(.Value) _
- Len(Replace(expression:=.Value, _
Find:=vbLf, _
Replace:="", _
compare:=vbTextCompare))) _
- CBool(.Value <> "")
End With

MsgBox myLen

End Sub

There is a difference how excel and VBA treat True. Excel uses +1 and VBA uses
-1. That's why the last portion is subtracted in code.

 

[Harry]

Solved.

 

[Harry]

Thanks Dave and sorry for skipped your post before ...

Now I uses "Justify" instead of wraptext to seperate the string to multi-row first, then loop the rows to find the Len(string) > 0
and rowcount, it's ok, and I will reference to your code for another solution, thanks for your help.