Extracting first name

QuestionEdit

QUESTION: This is Sami again with an aching problem. I have a long list of Destinations name out of which i need to extract the country name. the country name usually appears to be the first name of the destination name. Some example of the destination names are given bellow: AFGHANISTAN Kuwait-ROC-Fixed YEMEN(ARAB REP)ALL COUNTRY AUSTRALIA-Reg Armenia-Other-Mobile Qatar - Mobile Vodafo Afghanistan Cellular-Others Bolivia-Rural Angola Cellular-Unitel Belize,Belize City Argentina,Buenos Aires Brazil,Curitiba

so what i need is , when ever it will found either a space or a comma or a - or a parenthesis then it will extract the name before it.

i have developed a formula which looks like as follows: =IFERROR(LEFT(D24,IFERROR(IFERROR(IFERROR(FIND("-",D24)-1,FIND(",",D24)-1),FIND("(",D24)-1),FIND(" ",D24)-1)),D24) but it produces a result CHINA BUSINESS AREA for CHINA BUSINESS AREA-Reg instead of CHINA only.

Please help me in extracting the names.

Thank you for your kind help once again.

Sami

ANSWER: Try this - entered by ctrl/shift/enter, not just enter:

=IFERROR(LEFT(D24,MATCH(TRUE,CODE(UPPER(MID(D24,ROW($1:$100),1)))<65,0)-1),D24)

---------- FOLLOW-UP ----------

QUESTION: WOW!!!!! Its really creepy, can you pls pls pls tell me how the formula is working inside???? Thank you again for your kind help.

Humble Sami

AnswerEdit

CODE("A") is 65. CODE("B") is 66, etc. CODE("a") is 97. So to avoid the 97-122 range (122 for "z"), I cahnged the value to uppercase using the UPPER function. MID(D24,ROW(1:100),1) is each character of D24, since ROW(1:100) is {1;2;3;...;99;100}. So if D24 contains "Bolivia-Rural", this piece is {"B";"o";"l";"i";..etc} UPPER makes it {"B";"O";"L";"I";"V"...etc} CODE of each character makes it {66;79;76;73;86... etc} CODE() < 65 makes it {FALSE;FALSE;FALSE,... until it hits something under 65, which a comma would be, or a dash etc), so Qatar - Mobile has a space after the "r", and the 6th item in that list would be TRUE because CODE(" ") is 32, which is < 65. MATCH(TRUE,{....},0) would then be 6. The formula subtracts 1, so the MATCH returns 5. If this is all an error (AFGHANISTAN has no space or special chars, so all items would be FALSE and the MATCH(TRUE... would return an error), then it just returns the original, in this case AFGHANISTAN.

HTH