excel formatting of telephone numbers

[chipwdomi]

this is in the excel cell how it looks (954) 731-5100

see how the number here is actually all the way to the right in the cell (no cell alignment was used)

see there is no (  ) in the formula bar but it is in the cell, causing data problems when I extract them from another database. I can not seem to find a way to make them all the same

   number in cell   

number in formula bar

 this is a exact match in the cell and in the formula bar, this is how I prefer them, this is how the good data comes over from the other database I pull from. How can I get the 1st ones to be like the 2nd ones that match both the formula bar and the excel cell itself?

I'm fixing this in excel and I can not fix as we get the data sent to us like this, so what the move I make to correct the entire column. I wish format painter would do it, but no.  Please any suggestions?

[#2]

@chipwdomi 

In your examples in red, the cell value is a number. That is why you see it as a plain mumber in the formula bar. A special number format has been applied to it to make it appear as a phone number in the cell, but the value stored in the workbook is the plain number.

If you want a value such as (954) 731-5100 to be the stored value, it must be a text value, not a number.

I don't know why some values come over from the database as a number and others as a text value.

To convert all phone numbers to text values, you could run the following macro. Select the range with phone numbers first.

Sub ConvertPhoneNumbers()
    Dim rng As Range
    Application.ScreenUpdating = False
    For Each rng In Selection
        If IsNumeric(rng.Value) Then
            rng.Value = rng.Text
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

[#3]

@chipwdomi 

First one is number formatted with brackets and dashes. Second one is text. If you prefer texts you may use

=TEXT(A1,"[<=9999999]###-####;(###) ###-####")

to convert

[#4]

@Sergei Baklan 

I prefer numbers, is there a way to format the entire row in to the number and formatting I had?

(954) 563-5567        like this?

[#5]

@chipwdomi 

If in place that's by macro similar to one which @Hans Vogelaar  suggested, but in opposite direction. If to copy into another place that could be done by formulas, not sure what do you prefer.