MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How can I make an Array Formula search for a word in a cell

Posted by Jim Rickel on March 29, 2000 4:08 PM

Howdy Everyone

I have an Array Formula. I want it to look for a particular word in a cell, rather than selecting the entire contents of the cell. The E column of the Outlook sheet contains multiple single word states. One of those states is "note". I want to find the word "note" regardless of it's position in the cell and count that occurance. Is there a way to do this?

Where is my Formula , thanks to Mr. Excel for alot of help with it.



Posted by Jim Rickel on March 29, 2000 4:27 PM

Sorry that wasn't every clear

Howdy Again

My previous post wasn't very clear.

I quess what I am asking for is how can I make an array formula do a wildcard search on the range to see if that contain a particular word, rather that match the contents of a selected cell?

The cells in the E column of my spreadsheet contain various single word states. for example
E2 [bookmark][note][ui]
E3 [note]
E4 [ui]

I want to look for the word "note" anywhere in the cell and count those ocurrances. To add to all this i want it also check the C column to match a particular cell. That can obey the normal array formula format.

Here is my formula:
The C section is working, I just want the E section to look for "*note*" rather than =E$2 .

Thanks so much for any help

Posted by Mark on March 30, 2000 9:54 AM

Re: Sorry that wasn't every clear


It's not pretty, but the following will work for your text entries in a column, say column B in B5:B150.
In C5 enter =FIND("note",B5) and drag sown to fill through B150.
In D5, or anywhere you want it, enter =COUNTIF(C5:C150,"<>#VALUE!").