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

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:="", _
compa=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 wrote:

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

Solved.

"Harry" ¦b¶l¥ó±i¶K¤º®e¥D¦® ¤¤¼¶¼g...
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

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. :)



"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:="", _
compa=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 wrote:

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