counting spaces in a string
 

1. Is there a simple way to count the spaces in a long string?
2. If you have multiple spaces in a string, is there a simple way to
determine what position in the string, number 4 space is?

Thanks
xnman

counting spaces in a string
 

1. =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
2. =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"xnman" wrote in message
...
1. Is there a simple way to count the spaces in a long string?
2. If you have multiple spaces in a string, is there a simple way to
determine what position in the string, number 4 space is?

Thanks
xnman

counting spaces in a string
 

"xnman" wrote...
1. Is there a simple way to count the spaces in a long string?

Simplest:

n = Len(s) - Len(Application.WorksheetFunction.Substitute(s, " ", ""))

2. If you have multiple spaces in a string, is there a simple way to
determine what position in the string, number 4 space is?

You could use something like

n = InStr(1, Application.WorksheetFunction.Substitute(s, " ", Chr(127), _
inst), Chr(127))

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

counting spaces in a string
 

Bob, Thanks for your quick response. I apologize, but I was asking the
questions in reference to vba. Any suggestions on that for both questions?

Bob Phillips wrote:

1. =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
2. =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)

counting spaces in a string
 

Thanks guys! You're the best.
xnman

Harlan Grove wrote:
"xnman" wrote...

1. Is there a simple way to count the spaces in a long string?


Simplest:

n = Len(s) - Len(Application.WorksheetFunction.Substitute(s, " ", ""))


2. If you have multiple spaces in a string, is there a simple way to
determine what position in the string, number 4 space is?


You could use something like

n = InStr(1, Application.WorksheetFunction.Substitute(s, " ", Chr(127), _
inst), Chr(127))

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.