Formula - search cell for text and check if text is contained in a specified list

[excelstarter1]

Hello everyone,

I have a quick question. Using the FIND or SEARCH Function in Excel I can check wether a cell contains a specific content, e.g. =FIND("Denmark",A1) checks the cell A1 for the word Denmark and returns the position if the word is contained in the cell. Now I have a huge dataset with several entries. Each cell contains customer information, country, product etc. (I cannot seperate the info, there is no real logic behind it. e.g. one cell starts with the customer name, the other cell starts with the country). Now I want to extract the country information from each cell. My idea: I have a list of all countries in the world and want to check each cell content against this list of countries. As a result I want the formula to return a zero if nothing was found, or the name of the country if it is contained in the cell. So rather then saying "check the cell for the word, i.e. country, Denmark", I want to tell Excel "check each cell if one of the countries in my list is contained in the cell's content". I really don't have a clue how to approach this problem...

Would be great if one of you could help out!!

Thanks in advance!

Regards

 

[excelstarter1]

Re: Forumla - search cell for text and check if text is contained in a specified list

Thank you. Not all the data is separated with a comma ,

So sometimes it is "Customer, Country, Product" and sometimes "Customer/Product/Country" or even without any seperator. I believe the formula you suggested, does not take this into account, right?

 

[Marcelo Branco]

Re: Forumla - search cell for text and check if text is contained in a specified list

You must reorganize your data in a coherent way - unique separator. Maybe a macro can do it. I suggest you create a new thread asking how to rearrange the data.
Without this it is very difficult (if possible) to create a formula that works.

M.

 

[excelstarter1]

Re: Forumla - search cell for text and check if text is contained in a specified list

Ok, you are right. I just took some time to reorganize the data and now it is working almost perfectly. Thanks again for your time and help!!

Regards

 

[Marcelo Branco]

Re: Forumla - search cell for text and check if text is contained in a specified list

You are welcome.
I'm glad it's working fine now. Thanks for the feedback.

M.