How to Count String occurences

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" <[email protected]> wrote in
message
news:[email protected]...
> 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

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" <[email protected]> wrote in
> message
> news:[email protected]...
> > 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" <[email protected]> wrote in
message
news:[email protected]...
> 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" <[email protected]> wrote
>> in
>> message
>> news:[email protected]...
>> > 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
>>
>>
>>