Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Phone Number Splitting Macro

  1. #1
    Join Date
    16th October 2004
    Posts
    24

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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...

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Phone Number Splitting Macro

    That second formula is for cell E2, not E3.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th October 2004
    Posts
    24

    Re: Phone Number Splitting Macro

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Phone Number Splitting Macro

    Cheers!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Format Phone Number In TextBox
    By FRIEL in forum Excel General
    Replies: 7
    Last Post: May 31st, 2008, 00:04
  2. Dial Phone Number
    By EXCEL_VBA_LOVER in forum Excel General
    Replies: 17
    Last Post: July 21st, 2007, 01:10
  3. Format Phone number as it is being entered
    By Dave Godfrey in forum Excel General
    Replies: 5
    Last Post: March 8th, 2006, 15:01
  4. Replies: 2
    Last Post: July 27th, 2005, 13:52
  5. Spliting a phone number
    By Darren in forum Excel General
    Replies: 3
    Last Post: February 17th, 2005, 03:54

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
  •  
porno