Extract specific text from a cell

[Rami9]

Hi, I have list of countries including continent name of each country in column A, and I need to put (cut) the continent name of each country in column B as shown in the attached file.
Thanks

 

[AliGW]

Unless you have a reference list of continents somewhere, this won't be possible. Excel will not be able to determine the number of words in the country or continent with nothing to refer to.

Which version of Excel are you using?

 

[AliGW]

Create a lookup list (can be anywhere, even on a separate worksheet):

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	H​
1​	Lookup List
2​	Africa
3​	Asia
4​	Europe
5​	North America
6​	Oceania
7​	South America

Sheet: Sheet1

Then this in B2 copied down:

=IFERROR(LOOKUP(99999,SEARCH($H$2:$H$7,A2),$H$2:$H$7),"")

 

[Rami9]

Thanks, it works exactly as requested