If there is the word And in a cell, return Joint Venture

[jasonleewkd]

Hi all,


Could anyone help me with a formula that returns "JV" (when there is the word "And" in the referenced cell) or "Non-JV" (when there is no "And")

[pre]

"F.E.Lakeside Pte. Ltd., Fcl Topaz Pte. Ltd.
And Sekisui House, Ltd. "	JV
Mcl Land Limited	Non-JV

[/pre]

The formula I came up with in B1 and B2 reads

=IF(ISERROR(FIND("And",A1)),"Non-JV","JV")


Beside the method above, is there another way to do it incorporating CSE?

 

[Hui]

Jasonleewkd


The Find() function is case sensitive, whereas Search() isn't


so a simple change will sufice

=IF(ISERROR(SEARCH("And",A1)),"Non-JV","JV")

 

[Luke M]

Mind defining what you mean by CSE? There were too many possibilities for me to guess.

http://www.acronymfinder.com/CSE.html


Your current formula seems to work okay for the data you have. Hui's statement is correct, but I think his formula won't help you since it will find the "and" within the word "Land" and think it should be a JV.

 

[jasonleewkd]

Hi Luke, CSE means control shift enter.


Yes FIND works for the data I have, what tweaks do I have to do to the formula if I would like to use SEARCH instead?

 

[Luke M]

Thanks for clarifying jasonleewkd. Might I ask why you want to use an array formula if what you have is working already? Similarly, I don't think you'll want to switch to a SEARCH function if there's the possibility of the word "and" being contained in some of your other words. For implementation, just look at Hui's post where he replaced the FIND function with SEARCH.


Overall, I guess I'm wondering what your actual question is. It seems like your original post already contained the answer. =/