+ Reply to Thread
Results 1 to 8 of 8

How to split dialing codes into country code and area codes

  1. #1
    Registered User
    Join Date
    08-12-2015
    Location
    Windhoek, Namibia
    MS-Off Ver
    MS Office 2013 Professional
    Posts
    5

    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 ALASKA 1907
    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. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    08-12-2015
    Location
    Windhoek, Namibia
    MS-Off Ver
    MS Office 2013 Professional
    Posts
    5

    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.

    Thanks in advance.

    br

    Stefan
    Attached Files Attached Files

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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?
    Last edited by daffodil11; 08-12-2015 at 12:08 PM.

  5. #5
    Registered User
    Join Date
    08-12-2015
    Location
    Windhoek, Namibia
    MS-Off Ver
    MS Office 2013 Professional
    Posts
    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-12-2015
    Location
    Windhoek, Namibia
    MS-Off Ver
    MS Office 2013 Professional
    Posts
    5

    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. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #8
    Registered User
    Join Date
    08-12-2015
    Location
    Windhoek, Namibia
    MS-Off Ver
    MS Office 2013 Professional
    Posts
    5

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Country Codes rate comparison from different vendor
    By maagjoel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2013, 09:31 AM
  2. [SOLVED] Formula for Replacing Codes Indicating Geographic Area with Actual Name of Area
    By andywozhere in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2012, 08:53 AM
  3. I need Macro(VBA code) to Highlight Invalid Country Codes in a column
    By narendrabr in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-31-2012, 07:01 AM
  4. I need Macro(VBA code) to Highlight Invalid Country Codes in a column
    By narendrabr in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-27-2012, 12:16 PM
  5. [SOLVED] Adding country codes to a phone number... Please help
    By percyth1 in forum Excel General
    Replies: 1
    Last Post: 03-08-2012, 10:38 PM
  6. Replies: 3
    Last Post: 03-20-2010, 11:17 AM
  7. Add ‘country name’ column to worksheet from a list of country codes.
    By Ben Morton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2009, 09:24 AM

Bookmarks

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