IF LEFT FIND

[datastudent]

Hi,

I want to extract the name of the country in my set of data. All the names are in the beginning of the set of texts so I tried to use the LEFT and FIND function. However, It does not work on those two words country and those row that only has the country name alone.

Here's where I am right now.

Template_v1.xlsm
	B	C			F
1	Country	Outbound Destination
2	Albania	Albania Fixed			Albania
3	Albania	Albania Mobile			Albania
5	#VALUE!	Argentina			Argentina
6	Argentina	Argentina - Mobile			Argentina
7	#VALUE!	Armenia			Armenia
19	Bosnia&Herzegovina	Bosnia and Herzegovina			Bosnia&Herzegovina
20	Bosnia&Herzegovina	Bosnia and Herzegovina Mobile			Bosnia&Herzegovina
30	Costa	Costa Rica			Costa Rica
34	Cyprus	Cyprus Mobile			Cyprus
35	Czech	Czech Republic			Czech Republic
36	Czech	Czech Republic Mobile			Czech Republic
iBASIS_Outbound

Cell Formulas
Range	Formula
B2:B3,B5:B7,B19:B20,B30,B34:B36	B2	=IF(OR(C2="Bosnia and Herzegovina",C2="Bosnia and Herzegovina Mobile"),"Bosnia&Herzegovina",LEFT(C2,FIND(" ",C2)-1))

 

[Sam_D_Ben]

May be you can try,

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2, " Mobile", ""), " Fixed", ""), "-", ""))

 

[kevin9999]

Looks like you want to use an ampersand, and lose the "Republic" based on your sample data. Perhaps:

Book1
	A	B	C
1		Country	Outbound Destination
2		Albania	Albania Fixed
3		Albania	Albania Mobile
4		Argentina	Argentina
5		Argentina	Argentina - Mobile
6		Armenia	Armenia
7		Bosnia&Herzegovina	Bosnia and Herzegovina
8		Bosnia&Herzegovina	Bosnia and Herzegovina Mobile
18		Costa Rica	Costa Rica
22		Cyprus	Cyprus Mobile
23		Czech	Czech Republic
24		Czech	Czech Republic Mobile
Sheet1

Cell Formulas
Range	Formula
B2:B8,B18,B22:B24	B2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," Fixed","")," - Mobile","")," Mobile","")," and ","&")," Republic","")

 

[Peter_SSs]

Duplicate to post #6 here: Find the Country in a set of words

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.