Count Line Feed

[mrroland]

Hi,

is it possible to count the number of times a line feed (alt - enter) is used in a text string

for example the count of the following example would be four

Book1
	A	B	C	D
1	the quick brown fox jumps over the lazy dog the quick brown fox jumps over the lazy dog the quick brown fox jumps over the lazy dog
Sheet1

Cheers,
Roland

 

[Richard Schollar]

Hi

Give the following a try:

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

 

[mrroland]

Brilliant!

Thanks Richard

I was actually looking for a VBA solution. I guess i can store this temporarily in xl and then delete it afterwards. That would be ok

Hope i don't ask to much but is there any way i can detect this in VBA without entering the formula on the actual sheet

Cheers,
Roland

 

[Richard Schollar]

Hi Roland

Sure - you can use the same principle:

Dim iNum As Integer
iNum = len(Sheets("Sheet1").Range("A1").Value)-len(replace(Sheets("Sheet1").Range("A1").Value,chr$(10),""))

 

[mrroland]

Thanks a million!!!

ray:

Cheers,
Roland