Macro to format phone number

**cjamps** · 03-07-2017, 07:27 AM

I have a phone hotline where the called in phone numbers are emailed to me in the following format 999-999-9999. However when I do a live teleconference the phone numbers that are emailed to me are formated like so: (999)999-9999 - JANE DOE. I paste both sets of phone numbers into excel to get rid of the duplicates. I want to change the live teleconference format (delete the name and take away the parenthesis) to reflect the hotline format. Is there a simple way to do this either by find & replace or by creating a macro? Thanx

**gondal** · 03-07-2017, 07:58 AM

Hi try this

Please Login or Register  to view this content.

**JOHN H. DAVIS** · 03-07-2017, 10:04 AM

Maybe:

Please Login or Register  to view this content.

**mikerickson** · 03-07-2017, 10:14 AM

The formula = TEXT(LEFT(SUBSITUTE(A1,"-",""),10)+0, "000-000-0000") should do the trick.

**cjamps** · 03-07-2017, 10:28 AM

I used Mr. Davis's formula and the numbers that were in 999-999-9999 were left alone as expected, the numbers with the () were formatted without the name as expected however they were formatted 999-X999-999 with "X" being a space.

**JOHN H. DAVIS** · 03-07-2017, 10:48 AM

I wasn't aware there could be spaces within the phone numbers. Try:

Please Login or Register  to view this content.

**cjamps** · 03-07-2017, 09:31 PM

I tried it with the same results. This was my mistake. I also didn't realize there was a space. The format is (999) 999-9999 not (999)999-9999.

**JOHN H. DAVIS** · 03-08-2017, 08:15 AM

Perhaps:

Please Login or Register  to view this content.

**cjamps** · 03-09-2017, 07:11 AM

Worked like a charm.

Thanx tons!

**JOHN H. DAVIS** · 03-09-2017, 07:22 AM

You're welcome. Glad to help out and thanks for the feedback. Please review the Forum Rules and mark this thread as SOLVED.

Macro to format phone number

LinkBack

Thread Tools

Rate This Thread

Display

Macro to format phone number

Re: Macro to format phone number

Re: Macro to format phone number

Re: Macro to format phone number

Re: Macro to format phone number

Re: Macro to format phone number

Re: Macro to format phone number

Re: Macro to format phone number

Re: Macro to format phone number

Re: Macro to format phone number

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Phone number format - reference post (Formatting Textbox to type phone numbers only)

[SOLVED] Extract Following Format Phone Number

Phone number format in vba text box?

Change Phone number format

Format a Phone Number

Format change of phone number

how do I add phone number format as a permanent custom format?

Bookmarks

Bookmarks

Posting Permissions