Thread
:
Excel 2003 get text after wraptext problem
View Single Post
#
4
Posted to microsoft.public.excel.programming
Harry[_10_]
external usenet poster
Posts: 3
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
Reply With Quote
Harry[_10_]
View Public Profile
Find all posts by Harry[_10_]