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

 

[Crystalyzer]

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?

 

[Mike TheSpike]

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

 

[Crystalyzer]

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.

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
	A	B
1	Original Data	Cleaned Data post macro
2	(123) 456-7890	(123) 456-7890
3	(123) 456 - 7890	(123) 456-7890
4	123 456 - 7890	(123) 456-7890
5	1234567890	(123) 456-7890
6	123) 456 - 7890	(123) 456-7890
7	(123 456 - 7890	(123) 456-7890
8	+1(123) 456-7890	(123) 456-7890
9	1(123) 456-7890	(123) 456-7890
Sheet1

 

[Mike TheSpike]

@Crystalyzer​

Perfect!!!!!! You are awesome! You made my day very happy!​

Thanks a lot,
-Mike

 

[Crystalyzer]

You're welcome!

 

[Mike TheSpike]

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

 

[Crystalyzer]

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.

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

 

[Mike TheSpike]

Yes the actual value look like (###)###-#### no space after ")"#

My apology for the confusion.

 

[Crystalyzer]

no worries, the code above should do the trick. I was able to edit out the space after the close parenthesis as you requested.