How to find Country Code and phone number mismatch?

    I am trying to match a phone number(~4500 phone numbers) to a list of country codes in excel to know the country. the problem is that I want to match the first x characters of the phone number to the country code (where x is the length of the country code in the list) as not all the country codes have the same length (Example. US:1 UK: 44 Jordan:962, UAE:971).


    Phone Numbers Column looks as below:
    447789205094
    919445356808
    97156613071


    Country Code sheet contains in two columns
    [TABLE="class: text_table"]

    [tr]


    [td]

    1

    [/td]


    [td]

    USA

    [/td]


    [/tr]


    [tr]


    [td]

    44

    [/td]


    [td]

    UK

    [/td]


    [/tr]


    [tr]


    [td]

    91

    [/td]


    [td]

    India

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    971

    [/td]


    [td]

    UAE

    [/td]


    [/tr]


    [tr]


    [td]

    973

    [/td]


    [td]

    Bahrain

    [/td]


    [/tr]


    [/TABLE]


    I need to mark each number with the country name.
    Then, check this country name with country mentioned in address field.

    Hey,


    I think you might run into some problems here because some country codes have the same beginnings but different lengths, like The Dominican Republic's country codes are +1809, +1829, and +1849 - this could lead to a confusion with the US. Even worse there are countries that share one country code, e.g. USA & Canada both use +1, while Russia & Kazakhstan use +7


    If you have a finite amount of countries that could work. you could use vlookup in a (slightly) tedious nested formula.


    Code
    =IFERROR(VLOOKUP(NUMBERVALUE(LEFT(A1,4)),CountryCodes!A:B,2,0),IFERROR(VLOOKUP(NUMBERVALUE(LEFT(A1,3)),CountryCodes!A:B,2,0),IFERROR(VLOOKUP(NUMBERVALUE(LEFT(A1,2)),CountryCodes!A:B,2,0),IFERROR(VLOOKUP(NUMBERVALUE(LEFT(A1,1)),CountryCodes!A:B,2,0),"No Match"))))


    This formula assumes the telephone number to be in cell A1, the Formula could be in B1. The CountryCodes Sheet has the numbers in colum A and country names in Column B.


    It basically tries to match the leftmost 4 characters, then 3, then 2, then 1, but it will ignore 5 digit country codes, because I'm not sure how much overlap CountryCode & Local Code can have.


    Though I kinda would think of making a UDF if you understand VBA, could make the formula easier.


    Edit: I want mention that I found that formula on another board after a google search. just modified it slightly to explain it easier.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login