+ Reply to Thread
Results 1 to 6 of 6

Trying to find a formula or group of formula's to break down NPA NXX's MIN Ranges

  1. #1
    Registered User
    Join Date
    04-30-2013
    Location
    Dodge City, Kansas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Trying to find a formula or group of formula's to break down NPA NXX's MIN Ranges

    Hello,

    I receive excel files that I need to enter into a data base. The excel files include rows of telephone number ranges. Some telephone number ranges include the entire 10,000 number blocks and some don't. Some of these spreadsheets I receive have 1,000's of rows so that is why I am trying to automate this process. some examples are below. What I am trying to figure out is a way within excel that I can create additional columns of data based on a test of the starting and ending subscriber numbers. My thoughts have been to create a big IF statement, but have not been able to come up with the flow. Any ideas?

    thanks
    Bob

    I receive data in the following format.

    NPANXX starting Subscriber # Ending Subscriber #
    620561 0000 4999

    So what I am interested in doing is creating a way for me to that the data above and translate it into the following.

    6205610
    6205611
    6205612
    6205613
    6205614

    Another example would be

    NPANXX starting Subscriber # Ending Subscriber #
    620561 7999 8999

    This would become
    6205617
    6205618

    Another example would be

    NPANXX starting Subscriber # Ending Subscriber #
    620561 0000 9999

    For this one since the telephone number range includes the entire 10,000 block, it should be
    620561

  2. #2
    Registered User
    Join Date
    01-20-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Trying to find a formula or group of formula's to break down NPA NXX's MIN Ranges

    This is a sad task for the menial but if you have to put them out of work I suggest you wait and one of the people determined for fame (MVP) will assist you

  3. #3
    Registered User
    Join Date
    04-30-2013
    Location
    Dodge City, Kansas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Trying to find a formula or group of formula's to break down NPA NXX's MIN Ranges

    In this case in me that has the task. This would also allow me to validate my database which would be a great thing

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Trying to find a formula or group of formula's to break down NPA NXX's MIN Ranges

    show the excel file, without confidentional information.

    also add, in your sheet, the expected result
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    04-30-2013
    Location
    Dodge City, Kansas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Trying to find a formula or group of formula's to break down NPA NXX's MIN Ranges

    Good Morning!
    Thank you for looking at this.

    BobNPANXX.xlsx

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Trying to find a formula or group of formula's to break down NPA NXX's MIN Ranges

    I don't understand (see) what there has to be done.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1