How to count the number of 'spaces' in a cell.

Using Excel 2007 VBA I'm trying to find the number of spaces in a text string so I can assign the total to a variable.

I could probably create a loop for the length of the string then test each character for a space and then iterate the variable.

However, is there a simple built-in function that does the same?

Perhaps

Please Login or Register  to view this content.

Hi Pepe,

that certainly works on the worksheet but I get zero value if used in VBA:

namelen = Len(a1) - Len(WorksheetFunction.Substitute(a1, " ", ""))

Strangly, if I just test on MsgBox(Len(a1)) it displays 0, so it appears that I'm doing something wrong but I just can't see it!

Yes, I do have some text in A1 before you ask

---------- Post added at 11:44 AM ---------- Previous post was at 11:40 AM ----------

It works if I do this though

namelen = Len(ActiveCell.Value) - Len(WorksheetFunction.Substitute(ActiveCell.Value, " ", ""))

seems a bit over the top!

Try this

Formula:

Please Login or Register  to view this content.

Originally Posted by darayuk

The number of spaces in a cell can be counted simply by using the Split method:
Dim x() As String
x = Split(Cells(1, 1), " ") 'count number of spaces in cell A1
MsgBox UBound(x)

You can find a lot of excel vba example code at: http://www.worldbestlearningcenter.c...ds-example.htm

Sorted, thanks to you all for your guidance, thanks to darayuk for the best answer.