counting spaces in a string

  • Thread starter Thread starter xnman
  • Start date Start date
X

xnman

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
 
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)
 
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))
 
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?
 
Thanks guys! You're the best.
xnman

Harlan said:
...



Simplest:

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




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.
 
Back
Top