How to Count String occurences

[Chaplain Doug]

Excel 2003. Is there a quick way (a function perhaps) to count the number of
occurences of a substring within a string? I have address strings in which I
want to quickly count the number of occurences of a double space (" ") in
the string. Thanks for the help.

 

[Chip Pearson]

Try something like the following:


Dim S As String
Dim SearchString As String
Dim SubStringCount As Integer
S = "the quick brown fox"
SearchString = " " ' two spaces
SubStringCount = (Len(S) - Len(Replace(S, SearchString, ""))) /
Len(SearchString)
Debug.Print SubStringCount


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





message
news:[email protected]...

 

[bj]

If you are sure there are no space multiples greater than 2 you can use
=(len(a1)-len(substitute(a1," ",""))/2

 

[Chaplain Doug]

Thanks for the cool tip. Did you guys study mathematics? I've got degrees
in it, but my doltish brain didn't think of this. I AM getting old! God
bless.

Try something like the following:


Dim S As String
Dim SearchString As String
Dim SubStringCount As Integer
S = "the quick brown fox"
SearchString = " " ' two spaces
SubStringCount = (Len(S) - Len(Replace(S, SearchString, ""))) /
Len(SearchString)
Debug.Print SubStringCount


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





message

 

[Chip Pearson]

As a matter of fact, I do have a degree in mathematics. Haven't
used it in years, though.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message

Thanks for the cool tip. Did you guys study mathematics? I've
got degrees
in it, but my doltish brain didn't think of this. I AM getting
old! God
bless.