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.
=SUM(IF(Outlook!$C$2:$C$6=$C2,IF(Outlook!$E$2:$E$6=E$2,1,0),0))
Thanks
jim
jrickel@fusionone.com
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:
=SUM(IF(Outlook!$C$2:$C$6=$C2,IF(Outlook!$E$2:$E$6=E$2,1,0),0))
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
jim
jrickel@fusionone.com
Posted by Mark on March 30, 2000 9:54 AM
Re: Sorry that wasn't every clear
Jim
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!").
Mark
