Find the Country in a set of words

[datastudent]

Hi,

I don't know how to explain this clearly so please be patient with me.

In column C, I have a formula where I look for the "Fixed" and "Mobile" only. What I want is to also show the country name if in column A it only show the country name as shown below.

Template_v1.xlsm
	A		C
3	Outbound Destination		a
22	Australia		Australia
23	Australia Emergency
24	Australia Fixed		Fixed
26	Australia Mobile		Mobile
52	Brazil		Brazil
53	Brazil - EMS
54	Brazil - Freephone
55	Brazil - Mobile		Mobile
81	Czech Republic		Czech Republic
82	Czech Republic 112
83	Czech Republic Freephone
84	Czech Republic Mobile		Mobile
85	Czech Republic Shared
iBASIS_Outbound

Cell Formulas
Range	Formula
C23:C24,C26,C82:C85,C53:C55	C23	=IFERROR(ARRAYTOTEXT(REGEXEXTRACT($A23,"Mobile|Fixed",1)),"")

 

[Eric W]

If you have a lookup table of countries like this:

Book1
	A
1	Countries
2	Albania
3	Australia
4	Benin
5	Brazil
6	Czech Republic
7	Denmark
8	England
9
10
Sheet3

Then you can use a formula like this:

Book1
	A		C
3	Outbound Destination
22	Australia		Australia
23	Australia Emergency
24	Australia Fixed		Fixed
26	Australia Mobile		Mobile
52	Brazil		Brazil
53	Brazil - EMS
54	Brazil - Freephone
55	Brazil - Mobile		Mobile
81	Czech Republic		Czech Republic
82	Czech Republic 112
83	Czech Republic Freephone
84	Czech Republic Mobile		Mobile
85	Czech Republic Shared
Sheet2

Cell Formulas
Range	Formula
C22:C24,C26,C52:C55,C81:C85	C22	=LET(c,Sheet3!$A$2:$A$20,mf,{"Mobile","Fixed"},XLOOKUP(A22,c,c,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,A22)),mf,"",0),0))

 

[Peter_SSs]

Bit of a guess from your sample data, but could either of these work for you?

25 02 19.xlsm
	A	B	C	D
22	Australia		Australia	Australia
23	Australia Emergency
24	Australia Fixed		Fixed	Fixed
26	Australia Mobile		Mobile	Mobile
52	Brazil		Brazil	Brazil
53	Brazil - EMS
54	Brazil - Freephone
55	Brazil - Mobile		Mobile	Mobile
81	Czech Republic		Czech Republic	Czech Republic
82	Czech Republic 112
83	Czech Republic Freephone
84	Czech Republic Mobile		Mobile	Mobile
85	Czech Republic Shared
Country

Cell Formulas
Range	Formula
C22:C24,C26,C52:C55,C81:C85	C22	=IF(A22="","",IFNA(REGEXEXTRACT($A22,"Mobile|Fixed",1),IF(A22=LEFT(A23,LEN(A22)),A22,"")))
D22:D24,D26,D52:D55,D81:D85	D22	=IF(A22="","",IFNA(REGEXEXTRACT($A22,"Mobile|Fixed",1),IF(COUNT(MATCH(A22&"*"&{"Fixed","Mobile","Freephone","Emergency","EMS","Shared"},A23:A$1000,0)),A22,"")))

 

[datastudent]

Bit of a guess from your sample data, but could either of these work for you?

25 02 19.xlsm
	A	B	C	D
22	Australia		Australia	Australia
23	Australia Emergency
24	Australia Fixed		Fixed	Fixed
26	Australia Mobile		Mobile	Mobile
52	Brazil		Brazil	Brazil
53	Brazil - EMS
54	Brazil - Freephone
55	Brazil - Mobile		Mobile	Mobile
81	Czech Republic		Czech Republic	Czech Republic
82	Czech Republic 112
83	Czech Republic Freephone
84	Czech Republic Mobile		Mobile	Mobile
85	Czech Republic Shared
Country

Cell Formulas
Range	Formula
C22:C24,C26,C52:C55,C81:C85	C22	=IF(A22="","",IFNA(REGEXEXTRACT($A22,"Mobile|Fixed",1),IF(A22=LEFT(A23,LEN(A22)),A22,"")))
D22:D24,D26,D52:D55,D81:D85	D22	=IF(A22="","",IFNA(REGEXEXTRACT($A22,"Mobile|Fixed",1),IF(COUNT(MATCH(A22&"*"&{"Fixed","Mobile","Freephone","Emergency","EMS","Shared"},A23:A$1000,0)),A22,"")))

The first formula works for some but for some it does not.

Template_v1.xlsm
	A			D
3	Outbound Destination			a
49	Bolivia
50	Bosnia and Herzegovina			Bosnia and Herzegovina
60	Cambodia
61	Chile			Chile
66	China
67	Colombia			Colombia
92	Ecuador
93	El Salvador - Fixed			Fixed
iBASIS_Outbound

Cell Formulas
Range	Formula
D49:D50,D60:D61,D66:D67,D92:D93	D49	=IF(A49="","",IFNA(REGEXEXTRACT($A49,"Mobile|Fixed",1),IF(A49=LEFT(A50,LEN(A49)),A49,"")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A310,A388:A1048576	Cell Value	duplicates	text	NO

 

[datastudent]

If you have a lookup table of countries like this:

Book1
	A
1	Countries
2	Albania
3	Australia
4	Benin
5	Brazil
6	Czech Republic
7	Denmark
8	England
9
10
Sheet3

Then you can use a formula like this:

Book1
	A		C
3	Outbound Destination
22	Australia		Australia
23	Australia Emergency
24	Australia Fixed		Fixed
26	Australia Mobile		Mobile
52	Brazil		Brazil
53	Brazil - EMS
54	Brazil - Freephone
55	Brazil - Mobile		Mobile
81	Czech Republic		Czech Republic
82	Czech Republic 112
83	Czech Republic Freephone
84	Czech Republic Mobile		Mobile
85	Czech Republic Shared
Sheet2

Cell Formulas
Range	Formula
C22:C24,C26,C52:C55,C81:C85	C22	=LET(c,Sheet3!$A$2:$A$20,mf,{"Mobile","Fixed"},XLOOKUP(A22,c,c,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,A22)),mf,"",0),0))

I would want something more simpler without the lookup table but this one works best. Thank you!

 

[datastudent]

Follow up question:

How do I extract the Country only on the same data?

Column 1 is the data and Column 2 is the expected result.

ALBANIA FIXED	ALBANIA
ANGOLA MOBILE	ANGOLA
ARGENTINA - MOBILE	ARGENTINA
ARMENIA	ARMENIA
BOSNIA AND HERZEGOVINA MOBILE	BOSNIA AND HERZEGOVINA

 

[datastudent]

If you have a lookup table of countries like this:

Book1
	A
1	Countries
2	Albania
3	Australia
4	Benin
5	Brazil
6	Czech Republic
7	Denmark
8	England
9
10
Sheet3

Then you can use a formula like this:

Book1
	A		C
3	Outbound Destination
22	Australia		Australia
23	Australia Emergency
24	Australia Fixed		Fixed
26	Australia Mobile		Mobile
52	Brazil		Brazil
53	Brazil - EMS
54	Brazil - Freephone
55	Brazil - Mobile		Mobile
81	Czech Republic		Czech Republic
82	Czech Republic 112
83	Czech Republic Freephone
84	Czech Republic Mobile		Mobile
85	Czech Republic Shared
Sheet2

Cell Formulas
Range	Formula
C22:C24,C26,C52:C55,C81:C85	C22	=LET(c,Sheet3!$A$2:$A$20,mf,{"Mobile","Fixed"},XLOOKUP(A22,c,c,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,A22)),mf,"",0),0))

What if instead of getting the country name I want to it to return as "AAA"?

 

[Eric W]

Assuming you have a table of countries, you can do this:

Book1
	A	B	C	D	E	F	G
1						Countries	Code
2	ALBANIA FIXED	ALBANIA	AAA			ALBANIA	AAA
3	ANGOLA MOBILE	ANGOLA	BBB			ANGOLA	BBB
4	ARGENTINA - MOBILE	ARGENTINA	CCC			ARGENTINA	CCC
5	ARMENIA	ARMENIA	AAA			ARMENIA	AAA
6	BOSNIA AND HERZEGOVINA MOBILE	BOSNIA AND HERZEGOVINA	EEE			AUSTRALIA	DDD
7	AUSTRALIA - FIXED	AUSTRALIA	DDD			BOSNIA AND HERZEGOVINA	EEE
8	ZAIRE	ZAIRE	ZZZ			BOLIVIA	FFF
9	ANGOLA FIXED	ANGOLA	BBB			COLOMBIA	GGG
10						DENMARK	BBB
11						ETHIOPIA	AAA
12						ZAIRE	ZZZ
13
Sheet6

Cell Formulas
Range	Formula
B2:B9	B2	=XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$12,A2)),$F$2:$F$12,"",0)
C2:C9	C2	=XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$12,A2)),$G$2:$G$12,"",0)

The B formula will return the name. If you have a second column on your country table, like column G here, you can use the C formula to get AAA or whatever you prefer.

Incidentally, there's a formula available to earlier versions of Excel that's a bit of a trick, but it's shorter and still works:

=LOOKUP(2,1/SEARCH($F$2:$F$12,A2),$F$2:$F$12)

 

[Peter_SSs]

The first formula works for some but for some it does not.

What about the second formula I suggested?
If that also does not work (hard to tell when most of the rows of the sample data are hidden) then I am in agreement with Eric that a lookup table would be required.

 

[datastudent]

Assuming you have a table of countries, you can do this:

Book1
	A	B	C	D	E	F	G
1						Countries	Code
2	ALBANIA FIXED	ALBANIA	AAA			ALBANIA	AAA
3	ANGOLA MOBILE	ANGOLA	BBB			ANGOLA	BBB
4	ARGENTINA - MOBILE	ARGENTINA	CCC			ARGENTINA	CCC
5	ARMENIA	ARMENIA	AAA			ARMENIA	AAA
6	BOSNIA AND HERZEGOVINA MOBILE	BOSNIA AND HERZEGOVINA	EEE			AUSTRALIA	DDD
7	AUSTRALIA - FIXED	AUSTRALIA	DDD			BOSNIA AND HERZEGOVINA	EEE
8	ZAIRE	ZAIRE	ZZZ			BOLIVIA	FFF
9	ANGOLA FIXED	ANGOLA	BBB			COLOMBIA	GGG
10						DENMARK	BBB
11						ETHIOPIA	AAA
12						ZAIRE	ZZZ
13
Sheet6

Cell Formulas
Range	Formula
B2:B9	B2	=XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$12,A2)),$F$2:$F$12,"",0)
C2:C9	C2	=XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$12,A2)),$G$2:$G$12,"",0)

The B formula will return the name. If you have a second column on your country table, like column G here, you can use the C formula to get AAA or whatever you prefer.

Incidentally, there's a formula available to earlier versions of Excel that's a bit of a trick, but it's shorter and still works:

=LOOKUP(2,1/SEARCH($F$2:$F$12,A2),$F$2:$F$12)

Okay I will add the Country code. But I still want to show the "Fixed" and "Mobile". How do I combine that to the initial formula?

=LET(c,Note!$E$2:$E$80,mf,{"Mobile","Fixed"},XLOOKUP(A4,c,c,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,A4)),mf,"",0),0))