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

[Mike TheSpike]

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

 

[Mike TheSpike]

Nothing more to say. You are amazing!
Excellent work @Crystalyzer

​

Regards,
Mike

 

[Peter_SSs]

Another option to try. This also copes with the situation where the user might click the Cancel button on the Input Box.

Sub Format_Phone_Numbers()
  Dim RX As Object
  Dim Rng As Range, c As Range
  
  On Error Resume Next
  Set Rng = Application.InputBox(Title:="Phone Number Format Range", _
    Prompt:="Select the range (or type the address) of cells to pull into your phone number format rule", Type:=8)
  On Error GoTo 0
  If Not Rng Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "(\+?1\()|\D"
    For Each c In Rng
      c.Value = Format(RX.Replace(c.Value, ""), "(###)###-####")
    Next c
  End If
End Sub

 

[Mike TheSpike]

Hi @Peter_SSs,

You too are great! Thanks for your reply. I will use both of your codes.

Regards,
Mike

 

[Peter_SSs]

Hi @Peter_SSs,

You too are great! Thanks for your reply. I will use both of your codes.

Regards,
Mike

Cheers.