count number of instances of a text string in a cell

[lawrencef]

hi - does anyone know best way for vba macro or worksheet function to count the number of instances of a seach string within a cell for example if a cell contains following

Ghanimeh & Culbert P & MA

is there a simple way to count the number of instances of "&" symbol ie answer being 2!

thx lawrie

[rylo]

Lawrie

Something like

Please Login or Register  to view this content.

This will work for a single character. If you have a string that is more than 1 char long, then divide the result by the length of the char string.

rylo

[tigertiger]

hi - does anyone know best way for vba macro or worksheet function to count the number of instances of a seach string within a cell for example if a cell contains following

Ghanimeh & Culbert P & MA

is there a simple way to count the number of instances of "&" symbol ie answer being 2!

thx lawrie

To see the link: http://www.excelforum.com/excel-work...ml#post2004429

[Richard Schollar]

A possible VBA alternative would be:

Please Login or Register  to view this content.

Richard

[MatrixMan]

Re- the use of:

Please Login or Register  to view this content.

... nice lateral thinking .. I do like it

I would offer this caution though to those not familiar with arrays - this will return the correct answer only if Option Base 1 is not set (Option Base 0 being the default) ... overwise, the answer is

Please Login or Register  to view this content.

.

For those not familiar with using arrays, it's because UBound returns the index of the last element, and since Split separates out the substrings between delimiters, there is one more substring than there are delimeters ... so if the first substring is in array index 0 and the second in array index 1, then there must only be 1 delimeter (the "&" in this case) to create the 2 substrings and the "count" is the upper index of the array, which is 1. And this will also work if the & is the last character, because the last element is still populated with a zero length string.

Hope that helps.