Forum Discussion

chipwdomi's avatar
chipwdomi
Copper Contributor
Sep 20, 2021

excel formatting of telephone numbers

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?

 

 

 

excel

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 20, 2021

    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

    • chipwdomi's avatar
      chipwdomi
      Copper Contributor
      Sep 20, 2021

      SergeiBaklan 

       

      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?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 20, 2021

        chipwdomi 

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

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 20, 2021

    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

Resources