If you're using xl2k or higher, VBA has Replace. If you're using xl97 or below,
you can use application.substitute.
Option Explicit
Sub testme()
Dim SpaceCtr As Long
Dim myStr As String
myStr = "asdf qwer qwer"
'myStr = Worksheets("sheet1").Range("a1").Value
SpaceCtr = Len(myStr) - Len(Replace(myStr, " ", ""))
MsgBox SpaceCtr
'or
SpaceCtr = Len(myStr) - Len(Application.Substitute(myStr, " ", ""))
MsgBox SpaceCtr
End Sub
wrote:
I forgot to say that I'm in VBA and not in Excel.
Is the Substitute function working in VBA too cause I got an error
Thanks
--
Dave Peterson