If the country code is ALWAYS three characters long, then use the
following 2 formulas to get the country code:

=IF(ISERROR(SEARCH("Mobile",A2)),B2,LEFT(B2,3))

and area code:
=IF(ISERROR(SEARCH("Mobile",A2)),"",RIGHT(B2,LEN(B 2)-3))

--
HTH,
Dianne

HI Every one

I have a list in two coloumns. First coloumn has country name along
with area name. Second is is country code with area code.
I want to seperate country code from the area codes, evenif the
country name+ area name remains as it is. But i am unable to do so.
here is a example

Country NAME Country CODE
ALGERIA 213
ALGERIA Mobile 21361
ALGERIA Mobile 2137
AMERICAN SAMOA 684
AMERICAN SAMOA Mobile 6842
ANDORRA 376
ANDORRA Mobile 3763
ANGOLA 244
ANGOLA Mobile 244912
ANGOLA Mobile 244915
ANGOLA Mobile 244923


Now I want to make them in three coloumns

Country NAME Country CODE Area CODE
ALGERIA 213
ALGERIA Mobile 213
61
ALGERIA Mobile 213
7
AMERICAN SAMOA 684
AMERICAN SAMOA Mobile 684 2
ANDORRA 376
ANDORRA Mobile 376
3
ANGOLA 244
ANGOLA Mobile 244 912
ANGOLA Mobile 244 915
ANGOLA Mobile 244 923

see emaple attached as the above will not come properly formated on
post. I will really apprecuate any help on this.

Regards

JKB

Attachment filename: example.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=412189 ---
Message posted from http://www.ExcelForum.com/