Vlookup in a string

[ExGus]

I am trying to cleanse a user input list of countries of residence. in some cases the country is hidden in a string of other words like this "kobeshi hyogo japan"
I want to lookup from a list of defined countries I have using =VLOOKUP(A4&"*",'country list'!$A$1:$A$165,2,0) but it returns #N/A in the above example.

What is the best way to do it, I have also tried the wildcard at front as well i.e. =VLOOKUP("*"A4&"*",'country list'!$A$1:$A$165,2,0)

Thanks

 

[Peter_SSs]

Could you use/adapt something like this to clean up the data?

Excel Workbook
	A	B	C	D
1	Text	Country		List
2	Austria	Austria		Albania
3	France	France		Austria
4	kobeshi hyogo japan	Japan		Italy
5	Spain	Spain		France
6	abc South Korea	South Korea		Spain
7				South Korea
8				North Korea
9				Japan
10				Russia
11
Country

 

[ExGus]

Perfect, thanks

 

[Peter_SSs]

Perfect, thanks

You're welcome. Thanks for the follow-up.