count number of instances of string in a string

There is a user defined function that will do this nicely. A UDF means you create function in Excel that doesn't exist yet. This page has the code you need to install in your workbook and instructions at the bottom to do it quickly.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=235

Once it is installed, you can put your name in cell A1, and then the formula in B1 would simply be:

Please Login or Register  to view this content.

I don't know if it helps, but this UDF will also search for a character or character string in range of cells, too. Could be useful.

Originally Posted by lawrencef

hi - does anyone know how best to use worksheet functions 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!

I assume your data (the string) in cell A1
We should use the follow formula that combine LEN and SUBSTITUTE function (worksheet function)

PHP Code: 

=LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))