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

 

[Marcelo Branco]

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

See if this very simple example helps


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Text​

[/td][td]

Result​

[/td][td][/td][td]

Countries​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

John, Denmark, Product1​

[/td][td]

Denmark​

[/td][td][/td][td]

Brazil​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Brazil, Product2, Mike​

[/td][td]

Brazil​

[/td][td][/td][td]

Denmark​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Product3, Mary, France​

[/td][td]

France​

[/td][td][/td][td]

France​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Product2, Richards​

[/td][td]

0​

[/td][td][/td][td]

US​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Charles, US, ProductX​

[/td][td]

US​

[/td][td][/td][td][/td][/tr]
[/table]

Formula in B2 copied down
=IFERROR(LOOKUP(9.99E+307,SEARCH(D$2:D$5,A2),D$2:D$5),0)

To understand how the formula works see this excellent explanation by Aladin Akyurek
Lookup for a value in Unsorted data

M.

 

[excelstarter1]

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

Awesome, thanks for the quick reply Marcelo!

Just wondering, how would you transform the formula such that I can use it as a vlookup function? So instead of returning the full name of the country, I want to return a number or an abbreviation, e.g. France = FR. First column full name of the country, second column abbreviation.

I read the explanation by Aladin Akyurek. Thanks for the reference! Is the "9.99E+307" term within the forumla really necessary, if I am only comparing text instead of numbers/values? To be honest, that was the part, I did not get

 

[Marcelo Branco]

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

9.99E+307 is a big number (really big!). It's a short of 9.99999999999999E+307 that is the Largest allowed positive number

See the topic Calculation specifications and limits at
Excel specifications and limits - Excel

Aladin explains the role of this big number in the formula

To use country abbreviations try

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Text​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

Countries​

[/TD]
[TD]

Abbr​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

John, Denmark, Product1​

[/TD]
[TD]

DE​

[/TD]
[TD][/TD]
[TD]

Brazil​

[/TD]
[TD]

BR​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Brazil, Product2, Mike​

[/TD]
[TD]

BR​

[/TD]
[TD][/TD]
[TD]

Denmark​

[/TD]
[TD]

DE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Product3, Mary, France​

[/TD]
[TD]

FR​

[/TD]
[TD][/TD]
[TD]

France​

[/TD]
[TD]

FR​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Product2, Richards​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

US​

[/TD]
[TD]

US​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Charles, US, ProductX​

[/TD]
[TD]

US​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in B2 copied down
=IFERROR(LOOKUP(9.99E+307,SEARCH(D$2:D$5,A2),E$2:E$5),0)

M.

 

[excelstarter1]

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

That works perfectly, thanks!

One issue I just came across: when a name or product contains the countries abbreviation, e.g. US (sUSan, rUSsia) or CH = Switzerland (CHarles), I obviously get a wrong match. Is there a way to check for exactly the right word. That is US or CH (Switzerland) has only two letters/characters. I could use this property to check that it is not surrounded by other letters/characters. So "xxxxxx xxx / CH xxxx" = Switzerland would be right, but "xxxx Charles, xxx" should return the result 0 as no country is mentioned; the result Switzerland would be wrong. Do you have an idea?

The problem is that the data I have contains the full country name as well as country abbreviations, so I have to be able to match CH to Switzerland as well as the full name Switzerland to Switzerland.

I hope my explanation was not too confusing...

Thanks!

 

[Marcelo Branco]

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

Please, provide a small data sample for testing purposes along with expected results

M.

 

[excelstarter1]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Original data
[/TD]
[TD]Assign country using Excel formula[/TD]
[TD]RIGHT Country[/TD]
[TD]WRONG Country[/TD]
[/TR]
[TR]
[TD]Robert XXXX, Product 1, Russia[/TD]
[TD][FORMULA HERE][/TD]
[TD]Russia[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Susan XXXX, Italy, Product 2[/TD]
[TD][FORMULA HERE][/TD]
[TD]Italy[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]China, William XXXX, Product 1[/TD]
[TD][FORMULA HERE][/TD]
[TD]China[/TD]
[TD]Switzerland[/TD]
[/TR]
[TR]
[TD]Denmark, Charles XXXX, Product 3[/TD]
[TD][FORMULA HERE][/TD]
[TD]Denmark[/TD]
[TD]Switzerland[/TD]
[/TR]
</tbody>[/TABLE]

Hope that helps. "Wrong country" means the current formula returns the wrong country.

My dataset for the countries looks like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Countries (compare with formula)
[/TD]
[TD]Show this country
[/TD]
[/TR]
[TR]
[TD]Denmark
[/TD]
[TD]Denmark
[/TD]
[/TR]
[TR]
[TD]Germany
[/TD]
[TD]Germany
[/TD]
[/TR]
[TR]
[TD]France
[/TD]
[TD]France
[/TD]
[/TR]
[TR]
[TD]CH
[/TD]
[TD]Switzerland
[/TD]
[/TR]
[TR]
[TD]Italy
[/TD]
[TD]Italy
[/TD]
[/TR]
[TR]
[TD]US
[/TD]
[TD]USA
[/TD]
[/TR]
[TR]
[TD]USA
[/TD]
[TD]USA
[/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

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

Are you using the full name (Russia, Italy,...) or abbreviations (RU, IT...) to search?

M.

 

[excelstarter1]

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

Both, as the original data (unfortunately) does include the full name as well as abbreviations...

 

[Marcelo Branco]

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

Try

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Text​

[/td][td]

Formula​

[/td][td][/td][td]

Countries​

[/td][td]

Show​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Robert XXXX, Product 1, Russia​

[/td][td]

Russia​

[/td][td][/td][td]

Denmark​

[/td][td]

Denmark​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Susan XXXX, Italy, Product 2​

[/td][td]

Italy​

[/td][td][/td][td]

Germany​

[/td][td]

Germany​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

China, William XXXX, Product 1​

[/td][td]

China​

[/td][td][/td][td]

France​

[/td][td]

France​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Denmark, Charles XXXX, Product 3​

[/td][td]

Denmark​

[/td][td][/td][td]

CH​

[/td][td]

Switzerland​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Charlotte xxxx, CH, Product 4​

[/td][td]

Switzerland​

[/td][td][/td][td]

Italy​

[/td][td]

Italy​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Richard xxxxx, Product 6​

[/td][td]

0​

[/td][td][/td][td]

US​

[/td][td]

USA​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

US, ProductX, John​

[/td][td]

USA​

[/td][td][/td][td]

USA​

[/td][td]

USA​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td]

Russia​

[/td][td]

Russia​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td]

China​

[/td][td]

China​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in B2 copied down
=IFERROR(LOOKUP(9.99E+307,SEARCH(","&D$2:D$10&",",","&SUBSTITUTE(A2," ","")&","),E$2:E$10),0)

M.