Text formula which evaluates to True/ False

[Banker1]

Hoping someone here can help.

I'm working with a data set that includes a series of text strings I need to evaluate in order to "recode" the strings into a normalized list I can write formulas off of. For example:

Apple - TV 
Banana - Radio
Carrot - Web
Date - Print

I know how to write formulas to find a particular string if I wanted to rewrite the formula for each line:

Apple - TV =IF(FIND("TV",cell ref,1),"TV","")
Banana - Radio =IF(FIND("Radio",cell ref,1),"Radio","")
Carrot - Web =IF(FIND("Web",cell ref,1),"Web","")
Date - Print =IF(FIND("Print",cell ref,1),"Print","")

What I can't figure out is how to write a nested if to incorporate those formulas above into one in order to copy down the entire range... the FIND() formula returns a #Value! error if the test string isn't found. I'm looking for a formula which will return as True/ False error.

Any ideas?

 

[anandvarma]

try this

Excel Workbook
	A	B
1	Apple - TV	TV
2	Banana - Radio	Radio
3	Carrot - Web	Web
4	Date - Print	Print
Sheet1

 

[bosco_yip]

1] Use anandvarma's table

2] B1 formula copy down :

=LOOKUP(2,1/(FIND({"Apple","Banana","Carrot","Date"},A1)),{"TV","Radio","Web","Print"})

Regards

 

[Banker1]

Thanks for the suggestions. I should have specified that my data set may or may not have a dash separating the text and the values I'm looking for could be anywhere in the string, apologies.

Bosco, I like where your formula is going, any ideas how to make it less dependent on the other text in the string? I need to identify Tv, Radio, Print and Web regardless of what other text is in the string and regardless of the search string's position.

Thanks