Announcement

Collapse
No announcement yet.

Phone Number Splitting Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Phone Number Splitting Macro



    I need to set up a macro that will split telephone numbers into the area code and the remainder.

    I have a list of all available area codes - excluding the leading zero there are a mixture of 2,3,4 and 5 digit codes.

    If the attached is used as an example, the phone numbers in col C have been split out into the area code in col D and the remainder into Col E as per the area code in col A.

    I'm really not sure how to sort this one so if anyone can offer any help I would be really grateful! Thanks...
    Attached Files

  • #2
    Re: Phone Number Splitting Macro

    You don't need a macro for this. Put this formula in cell D2 and fill down:

    =IF(ISNUMBER(MATCH(VALUE(LEFT(C2,5)),A$2:A$200,0)),VALUE(LEFT(C2,5)),IF(ISNUMBER(MATCH(VALUE(LEFT(C2,4)),A$2:A$200,0)),VALUE(LEFT(C2,4)),IF(ISNUMBER(MATCH(VALUE(LEFT(C2,3)),A$2:A$200,0)),VALUE(LEFT(C2,3)),IF(ISNUMBER(MATCH(VALUE(LEFT(C2,2)),A$2:A$200,0)),VALUE(LEFT(C2,2)),""))))

    and this formula in E3 and fill down:

    =IF(D2="","",VALUE(RIGHT(C2,LEN(C2)-LEN(D2))))

    Note: in the first formula I have allowed 200 telephone exchanges - expand this as necessary (4 times). You may get trouble if column A digits of one length equal the first few digits of another length...
    .

    Comment


    • #3
      Re: Phone Number Splitting Macro

      That second formula is for cell E2, not E3.
      .

      Comment


      • #4
        Re: Phone Number Splitting Macro

        That works perfectly!! Thanks for your help!!!!

        Comment


        • #5
          Re: Phone Number Splitting Macro

          Cheers!
          .

          Comment


          • #6


            Re: Phone Number Splitting Macro

            In D2,

            =INDEX($A$2:$A$11,LOOKUP(9.9999999E+307,CHOOSE({1,2,3,4},MATCH(LEFT(C2,2)+0,$A$2:$A$11,0),MATCH(LEFT(C2,3)+0,$A$2:$A$11,0),MATCH(LEFT(C2,4)+0,$A$2:$A$11,0),MATCH(LEFT(C2,5)+0,$A$2:$A$11,0))))

            In E2,

            =SUBSTITUTE(C2,D2,"")+0

            HTH
            Kris

            ExcelFox

            Comment

            Working...
            X