Count occurances of a string within a string (VBA WORD) 1

[mdav2]

I am trying to count the number of instances a space appears in a string. I am sure I used a in built command to do this but can't for the life of me remeber what it is. The worst thing is I only deleted the code this morning because I thought I didn't need it.

I am trying to avoid writing a loop because I know I did it with a VBA function. Anyone point me in the right direction?

Many thanks,

Mark Davies
Warwickshire County Council

 

[HarleyQuinn]

To be honest, I'd probably use a regular expression for this (but it's overkill for a small string to check).

I've not used Word VBA for a while and I can't think of a single function to do this, so here's a version using built in VBA function and no loop for you. You pass in the string you want to check and the character(s) you're looking for:

Private Function HowManyThisChar(string1 As String, stringtofind As String) As Long

    HowManyThisChar = Len(string1) - Len(Replace(string1, stringtofind, ""))

End Function

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[mdav2]

Magic! I am sure I must have done it somehow with REPLACE. Just can't believe I forgot all about it in a few hours. Perhaps I am going a bit senile, in my 30's...

Deserves a star!

Mark Davies
Warwickshire County Council

 

[fumei]

Nice and simple. I like it.

Just as a possibly interesting point (and because I am the curious type...)

I just tested HarleyQuinn's Replace function against a looping function - but both of course doing the same thing, getting the count of an input string within a given string. I got a start and end time count for the processing.

On a 800 page document (filled with "The quick brown fox jumps over the lazy dog." using the =Rand function), and using the whole document as "string1"; and using "fox" as the search string.....there was ZERO time difference. They both took the same amount of time.

Which surprised the heck out of me! I thought for sure the Replace would beat ANY looping counter. On the other hand, the Replace (while not counting per se) is sort of doing a start/stop processing.

Start to find the search string
Stop and replace the found search string with the replace string.
Start to find the next search string.

In any case, on a 800 page document, it made no detectable difference. Which again, did in fact surprise me.

Gerry

 

[strongm]

Or:

Private Function HowManyThisChar(string1 As String, stringtofind As String) As Long

HowManyThisChar = ubound(split(string1, stringtofind))

End Function


I'll leave it to Gerry to test the timings on this ...

 

[HarleyQuinn]

I should add that my solution in it's current form won't correctly handle strings to find that are more than one character long (to get around this just divide the answer by the length of the find string).

Did a little bit of testing on my own with these solutions and it seemed that mine (with the aforementioned update) seemed to run at consistently similar times to strongm's and a regex solution seemed to perform quite significantly better the majority of the time.

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com