+ Reply to Thread
Results 1 to 4 of 4

Country code maping with Caller number

  1. 04-04-2012, 03:16 AM #1
    naveen4pundir
    naveen4pundir is offline
    Registered User
    Join Date
    03-23-2012
    Location
    Haryana, Delhi NCR, Bangalore.
    MS-Off Ver
    Excel 2003, 2007
    Posts
    44

    Country code maping with Caller number

    Hi,
    Pls help me on this.

    I have 2 excel sheets format is below mention. In sheet one i have some number with ISD code and in secound sheet i have country name and Code. now i want to match Country name in sheet one but my porblem is that there are some code are in 1 degit and some code are in 2,3,4,5 degits. If you see in example 220 is belong to Gambia and 224 belong to Guinea so first i want to match all code who is having 1 degit code second who is having 2 degit than 3 degit, 4 degit respectively.

    As per Ist number 54911678222709 Formula will check Ist degit 5 if it is Match than ok else chek 54 if it's match than ok else check 549.

    Sheet One:-
    Caller Number
    54911678222709
    5493426121222
    971501364886
    9715013648860
    2207771650
    22430919600
    23190000368
    23222273217

    Sheet two:-
    Country Code Name
    54 Argentina
    54 Argentina
    971 Emiratos Árabes Unidos
    971 Emiratos Árabes Unidos
    220 Gambia
    224 Guinea
    231 Liberia
    232 Sierra Leona


    is it possiable with combination of some formula like Len, vlookup etc?
    Thanx & Regards,
    Naveen Pundir
    Reply With QuoteRegister To Reply
  2. 04-04-2012, 03:46 AM #2
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Country code maping with Caller number

    Maybe
    In Sheet1 B2
    Please Login or Register  to view this content.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
    Reply With QuoteRegister To Reply
  3. 04-04-2012, 03:51 AM #3
    Andrew-R
    Andrew-R is offline
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Country code maping with Caller number

    From your sample data this formula, in B2 of sheet1, works for me:

    =INDEX(Sheet2!B:B,MATCH(TRUE,INDEX(ISNUMBER(FIND(Sheet2!$A$1:$A$9,A2)),0),0))
    Reply With QuoteRegister To Reply
  4. 04-04-2012, 05:24 AM #4
    naveen4pundir
    naveen4pundir is offline
    Registered User
    Join Date
    03-23-2012
    Location
    Haryana, Delhi NCR, Bangalore.
    MS-Off Ver
    Excel 2003, 2007
    Posts
    44

    Re: Country code maping with Caller number

    Thanx a lot ..it's working.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1