Excel 2003 get text after wraptext problem
 

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

Excel 2003 get text after wraptext problem
 

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

Excel 2003 get text after wraptext problem
 

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

Excel 2003 get text after wraptext problem
 

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