# How to split dialing codes into country code and area codes

1. ## How to split dialing codes into country code and area codes

Good day,

I have a sheet1 that has the following two columns: column 1 = Country name, column 2 = Dialling code e.g:

Column 1 Column 2
USA HAWAII 1808
USA TOLLFREE ACCESS 1800
USA TOLLFREE ACCESS 1844
USA TOLLFREE ACCESS 1855
USA TOLLFREE ACCESS 1866
USA TOLLFREE ACCESS 1877
USA TOLLFREE ACCESS 1880
USA TOLLFREE ACCESS 1881
USA TOLLFREE ACCESS 1882
USA TOLLFREE ACCESS 1888
UZBEKISTAN 998
VANUATU 678
VATICAN CITY 379
VENEZUELA 58
VENEZUELA CARACAS 58212
VENEZUELA MOBILE 584
VIETNAM 84
VIETNAM MOBILE 8490
VIETNAM MOBILE 8491
VIETNAM MOBILE 8495
WALLIS AND FUTUNA 681
YEMEN 967
ZAMBIA 260
ZAMBIA MOBILE 2609
ZAMBIA MOBILE - MTN 26096
ZAMBIA MOBILE - ZAIN 26097
ZAMBIA MOBILE - ZAMTEL 26094
ZAMBIA MOBILE - ZAMTEL 26095
ZIMBABWE 263
ZIMBABWE MOBILE - ECONET 26377
ZIMBABWE MOBILE - ECONET 26378
ZIMBABWE MOBILE - NETONE 26371
ZIMBABWE MOBILE - TELECEL 26373
ZIMBABWE VOIP 26386
ZIMBABWE VOIP - LIQUID 2638677

In Sheet 2, I have one column with country codes : eg :

USA 1
ZIMBABWE 263
WALLIS AND FUTUNA 681
YEMEN 967
ZAMBIA 260
VIETNAM 84

How can I split the dialling code in Sheet 1 in to country code and area code, by somehow matching the country code from sheet 2 to the left side of column 2 in sheet 1. Please bear in mind that the country code can be between 1 and 3 digits.

Assistance would be highly appreciated.

br

Stefan

2. ## Re: How to split dialing codes into country code and area codes

In Column C of Sheet1, just put:

=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!\$B\$1:\$B\$6,A1)),Sheet2!\$B\$1:\$B\$6),"")&B1

and paste down. Then copy the values of C into B.

3. ## Re: How to split dialing codes into country code and area codes

Hi,

Thank you for your prompt response. If I use your formula in Sheet 1 Column C, I get the some result as in Column. I have attached the test file and indicates the desired result in Column D and E.

br

Stefan

4. ## Re: How to split dialing codes into country code and area codes

I see, I wasn't aware you wanted them separately.

This should do it:

=IFERROR(SUBSTITUTE(B11,LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!\$A\$1:\$A\$219,A11)),Sheet2!\$B\$1:\$B\$219),""),"")

The information seems to crisscross: is the area code and country code the same for Afghanistan?

5. ## Re: How to split dialing codes into country code and area codes

Hi,

Thanks a million for the assistance, much appreciated. Your formula works perfectly for extracting the area code from the dialling code except for Albania Tirane(the formula extracted the complete dialling code and not country code 355 and area code 4) and for all dialling codes that starts with 1. Dialling codes starting with 1 e.g. American Samoa (1684) should be as following ( 1 country code and 684 for area code). All dialling codes starting with a 1, should be classified under sheet 2 = United States, meaning 1 = country code and e.g. 242 "Area Code" = Bahamas. I have highlighted a few samples that are currently not correct in attached sheet.

Sheet 1 contains dialing codes per country as well as dialing codes for different operators for the some country. The country code should always the same per country e.g. :

AFGHANISTAN 93
AFGHANISTAN MOBILE 937

AFGHANISTAN 93 93 = country code no area code
AFGHANISTAN MOBILE 937 93 = country code 7 = area code

Sheet 2 is just a reference sheet for all country codes.

br

Stefan

6. ## Re: How to split dialing codes into country code and area codes

Hi,

The problems seems to be with the country name lookup in both sheets. For example ALBANIA TIRANE gets 2 results , 1 X ALBANIA , 1 X IRAN (within the word TIRANE). Will it be possible to do a pattern matching on the Dialing code(Sheet 1) vs the Country Code(Sheet 2) omitting the country names?

br

Stefan

7. ## Re: How to split dialing codes into country code and area codes

I can make it search left to right with:

=IFERROR(SUBSTITUTE(B2,LOOKUP(2,1/MATCH(\$H\$2:\$H\$220&"*",A2,0),Sheet1!\$I\$2:\$I\$220),""),"")

but I can't make it match partial sometimes, and not at others.

``Please Login or Register  to view this content.``
Searching for Samoa in Sheet1 requires a partial mid-string match. I can't allow a mid string search for Samoa, that won't also get a mid-string match for Iran in Albania Tirane.

8. ## Re: How to split dialing codes into country code and area codes

Hi,

Thank you for the response. Is it not possible to use the Dialing code(Column B) from Sheet 1 and match it from left to right with Column B in sheet 2. E.g. If the following Dialing code from Sheet 1 - Column B : 35569 is matched to the country codes on sheet 2 Column B from the left to the right and it matched with 355 country code (Albania), it should return 355 as country code and the digits left as area code. If this will work, the country names are complete ignored and can not produce incorrect results. Please advise, if possible.

br

Stefan

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1