Phone formatting (###)###-####

Mike TheSpike

New Member
Joined
Jul 12, 2021
Messages
40
Office Version
  1. 2010
Platform
  1. Windows
Hi Guys,

Need help in macro for the phone format (###)###-####. Samples phone numbers to be formatted: (123) 456-7890, (123) 456 - 7890, 123 456 - 7890, 1234567890, 123) 456 - 7890, (123 456 - 7890 , +1(123) 456-7890, 1(123) 456-7890
Is it okay to assigned that macro in shortcut key? The way I used my macro is like this I have my macros in fix workbook and another workbook open which is my working file, every time I used shortcut keys(assigned macros) it works in my working file because the workbook where the macros were saved was opened.

Thanks to your amazing skills,
Regards,
Mike
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Mike do you want to run the macro on one phone number at a time or will you be selecting a range of phone numbers to clean up?
 
Upvote 0
Hi @Crystalyzer
I prefer the macro on one phone number.
But it is much appreciated if there is also for a range for me to know how it works and for future reference. The reason i prefer the one phone is because sometimes there are international phone numbers that are in different format if in that case we just copy the international phone as is and no format needed.

Thank you so much,
Mike
 
Upvote 0
The code below will remove all characters you supplied that are not numeric so that the phone number format can be applied to the cell and it will appear in the format (###) ###-####.

This macro will change the data in the cell or cells selected (must be adjacent cells with no gaps if multiple cells are selected). You can select multiple columns as well, but again, all the data must be continuous. The macro will apply the phone number format to the cells selected.

VBA Code:
Sub FormatPhoneNumber()
    Dim Rng As Range
    Dim Ph As String
    Set Rng = Application.InputBox( _
      Title:="Phone Number Format Range", _
      Prompt:="Select the range of cells to pull into your phone number format rule", _
      Type:=8)
        
    With Rng
        For j = .Column To .Column + .Columns.Count - 1
            For i = .Row To .Row + .Rows.Count - 1
                Ph = Cells(i, j).Value
                Ph = Replace(Ph, "+1(", "", 1)
                Ph = Replace(Ph, "+1", "", 1)
                Ph = Replace(Ph, "+", "", 1)
                Ph = Replace(Ph, "1(", "", 1)
                Ph = Replace(Ph, "(", "", 1)
                Ph = Replace(Ph, " ", "", 1)
                Ph = Replace(Ph, ")", "", 1)
                Ph = Replace(Ph, "-", "", 1)
                
                With Cells(i, j)
                    .Value = Ph
                    .NumberFormat = "[<=9999999]###-####;(###) ###-####"
                End With
            Next i
        Next j
    End With
End Sub

The Orignal data is in column A and the cleaned data is in column B which is formatted with the special format Phone Number
Book1
AB
1Original DataCleaned Data post macro
2(123) 456-7890(123) 456-7890
3(123) 456 - 7890(123) 456-7890
4123 456 - 7890(123) 456-7890
51234567890(123) 456-7890
6123) 456 - 7890(123) 456-7890
7(123 456 - 7890 (123) 456-7890
8+1(123) 456-7890(123) 456-7890
91(123) 456-7890(123) 456-7890
Sheet1
 
Upvote 0
Hi @Crystalyzer
One more thing, is it okay to have the phone format to be like this (123)456-7890 upon seeing the output is with ( ) and - but when i copied the phone numbers the ( ) and - is missing is this because of the .NumberFormat = "[<=9999999]?

Thanks,
Mike
 
Upvote 0
So you don't want the excel formatting you want the actual value to look like (###) ###-####?

if so, I updated the code to check that only numbers remain after all the replace operations and then I insert the special characters you want in the order you want them and make the value (###) ###-####. I have commented out setting the number format.

VBA Code:
Sub FormatPhoneNumber()
    Dim Rng As Range
    Dim Ph As String
    Set Rng = Application.InputBox( _
      Title:="Phone Number Format Range", _
      Prompt:="Select the range of cells to pull into your phone number format rule", _
      Type:=8)
       
    With Rng
        For j = .Column To .Column + .Columns.Count - 1
            For i = .Row To .Row + .Rows.Count - 1
                Ph = Cells(i, j).Value
                Ph = Replace(Ph, "+1(", "", 1)
                Ph = Replace(Ph, "+1", "", 1)
                Ph = Replace(Ph, "+", "", 1)
                Ph = Replace(Ph, "1(", "", 1)
                Ph = Replace(Ph, "(", "", 1)
                Ph = Replace(Ph, " ", "", 1)
                Ph = Replace(Ph, ")", "", 1)
                Ph = Replace(Ph, "-", "", 1)
               
                If Len(Ph) = 10 Then
                    Ph = "(" & Mid(Ph, 1, 3) & ")" & Mid(Ph, 4, 3) & "-" & Mid(Ph, 7, 4)
                End If
               
                With Cells(i, j)
                    .Value = Ph
                    '.NumberFormat = "[<=9999999]###-####;(###) ###-####"
                End With
            Next i
        Next j
    End With
End Sub
 
Last edited:
Upvote 0
Solution
Yes the actual value look like (###)###-#### no space after ")"#

My apology for the confusion.
 
Upvote 0
no worries, the code above should do the trick. I was able to edit out the space after the close parenthesis as you requested.
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top