Checking a cell to see if it has three possible words?

species8514

Board Regular
Joined
Oct 4, 2006
Messages
98
I have a cell which may possibly contain the names Mick or Paul or Bryn, and i want to create a formula which checks and returns a true or false value, if it finds a match.

I have been able to create one that checks for one name, but as soon as i try and nest a second, it messes up.

Any suggestions?

Thx guys.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Maybe:

IF(OR(A1="Mick",A1="Paul",A1="Bryn"),TRUE,FALSE)

lenze
 

species8514

Board Regular
Joined
Oct 4, 2006
Messages
98
Thanks!

That works well, although i was wondering if there is a way i can search the cell for the names? The above works if there is just the name on it's own, but if it's within a sentence, for example, Bryn made tea, it wont obviously find it. I guess i need a SEARCH function?

Thanks again guys
 

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
You could try this, Not an expert but it seems to do what you requested.

=IF(OR(SEARCH("Name1",A1),SEARCH("Name2",A1),SEARCH("Name3",A1)),"True","False")

Replace the Name1,Name2,Name3 with your selected Criteria

If it doesnt meet all criteria it will return a #VALUE in the Cell so u can distinguise the ones that do and dont meet the criteria, im sure u can get better than the above but im far from expert at this!!!

Hope it helps anyways.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Have a look at SEARCH and FIND functions. FIND is case sensitive

lenze
 

Forum statistics

Threads
1,141,680
Messages
5,707,788
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top