Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Phone Number Splitting Macro

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

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
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. Super Moderator
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. Member
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. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Phone Number Splitting Macro

Cheers!

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
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

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

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