Number of space in a string

K

kathy.aubin

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,
 
L

L. Howard Kittle

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
 
D

Dave Peterson

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

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

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

Biff

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

<space><space>try<space><space>this<space><space>

Safer to trim first:

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

Biff
 
K

kathy.aubin

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
 
D

Dave Peterson

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
 
Top