Hi,

I'm trying to find a way to count the number of space in a given
string. Is there any function that can do that?

Thanks for your help,

Hi Kathy,

Try this.

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

If you want to know the number of WORDS in the string:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

HTH
Regards,
Howard

wrote in message
ps.com...
Hi,

I'm trying to find a way to count the number of space in a given
string. Is there any function that can do that?

Thanks for your help,

=(len(a1)-len(substitute(a1," ","")))/len(" ")

Since you're only counting a single character, you could eliminate the
denominator:

=len(a1)-len(substitute(a1," ",""))

wrote:

Hi,

I'm trying to find a way to count the number of space in a given
string. Is there any function that can do that?

Thanks for your help,

--

Dave Peterson

If you want to know the number of WORDS in the string:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

<space<spacetry<space<spacethis<space<space

Safer to trim first:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Biff

"L. Howard Kittle" wrote in message
. ..
Hi Kathy,

Try this.

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

If you want to know the number of WORDS in the string:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

HTH
Regards,
Howard

wrote in message
ps.com...
Hi,

I'm trying to find a way to count the number of space in a given
string. Is there any function that can do that?

Thanks for your help,

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

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