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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

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





"Chaplain Doug" wrote in
message
...
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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

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.

"Chip Pearson" wrote:

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





"Chaplain Doug" wrote in
message
...
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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

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


"Chaplain Doug" wrote in
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.

"Chip Pearson" wrote:

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





"Chaplain Doug" wrote
in
message
...
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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

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

"Chaplain Doug" wrote:

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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org