Count the occurrences of a text string?

**New2MSExcel** · 11-04-2009, 06:25 AM

Hello, I am looking for your help in a problem i have with MS Excel,

The Spreadsheet includes four worksheets.

The first worksheet includes staff suggestions, the date they were submitted and who submitted the suggestions.

In the column 'raised by', the cell values are:

C1: John Smith
C2: John Smith, Iain Mills.

So in this example, i am looking to count the amount of times 'John Smith appears' or the amount of times 'Iain Mills' appears. John smith should be 2 and Iain mills should result in one.

However, I am only able to produce a formula to count the occurence if there is only one name in the cell but I am looking to count the amount of times the name appears in the column.

Any help with this would be great.

**Andy Pope** · 11-04-2009, 06:35 AM

If you create a table of staff in column G you can use this array formula.
Commit using CTR+SHIFT+ENTER

G1: =John Smith
I1: =COUNT(--(FIND(G1,$C$1:$C$2)>0))

**DonkeyOte** · 11-04-2009, 06:41 AM

You could perhaps use COUNTIF with wildcards ?

=COUNTIF($C$1:$C$2,"*"&$G1&"*")

**Jack in the UK** · 11-04-2009, 07:23 AM

Your need to tinker with this, bit fun some VBA for you, best stick with the functions if at all possible as with VBA you can end up in a mess quick

Edit the range is probably what you need to do

Please Login or Register  to view this content.

**DonkeyOte** · 11-04-2009, 07:47 AM

Jack in the UK, I'm not quite sure that does as you expected... I think perhaps you meant to alter your +1 to be a test based on the UBound of myVar ?

**Jack in the UK** · 11-04-2009, 09:52 AM

Originally Posted by DonkeyOte

Jack in the UK, I'm not quite sure that does as you expected... I think perhaps you meant to alter your +1 to be a test based on the UBound of myVar ?

Ermm yep thats a b ad one -- oppppssss

Try this?

Please Login or Register  to view this content.

**Jbentley** · 10-14-2010, 03:07 AM

I know this is an old thread, but I finally got around to writing a udf for counting the next of occurences of a string within another string.

the below is what I got to, a bit rough but does what it should

Please Login or Register  to view this content.

**DonkeyOte** · 10-14-2010, 03:12 AM

If you want to return the Count of one string within another then you can actually use a Substitute and test length of substituted string versus original and divide the difference by length of sub string removed, eg:

Please Login or Register  to view this content.

where A1 holds string being searched for and A2 the string being compared.

The same logic can be applied in terms of VBA if needed.

Irrespective of whether you use VBA/formula it's often nec. to apply some sort of delimiter to both strings to avoid false positive matches (not illustrated here).

**robbyvegas** · 10-24-2010, 08:47 PM

You rock. I was looking for a similar solution and using wildcards in the search string worked perfectly! Thank you sir.

Count the occurrences of a text string?

LinkBack

Thread Tools

Rate This Thread

Display

Count the occurrences of a text string?

Re: Count the occurrences of a text string?

Re: Count the occurrences of a text string?

Re: Count the occurrences of a text string?

Re: Count the occurrences of a text string?

Re: Count the occurrences of a text string?

Re: Count the occurrences of a text string?

Re: Count the occurrences of a text string?

Re: Count the occurrences of a text string?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions