excel formatting of telephone numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2768342%22%20slang%3D%22en-US%22%3Eexcel%20formatting%20of%20telephone%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2768342%22%20slang%3D%22en-US%22%3E%3CP%3Ethis%20is%20in%20the%20excel%20cell%20how%20it%20looks%20(954)%20731-5100%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22chipwdomi_8-1632165927985.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311643iFD99F88B3F536A9D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22chipwdomi_8-1632165927985.png%22%20alt%3D%22chipwdomi_8-1632165927985.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Esee%20how%20the%26nbsp%3Bnumber%20here%20is%20actually%20all%20the%20way%20to%20the%20right%20in%20the%20cell%20(no%20cell%20alignment%20was%20used)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22chipwdomi_1-1632164445632.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311631i9C4D54934E0F2D70%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22chipwdomi_1-1632164445632.png%22%20alt%3D%22chipwdomi_1-1632164445632.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Esee%20there%20is%20no%20(%26nbsp%3B%20)%20in%20the%20formula%20bar%20but%20it%20is%20in%20the%20cell%2C%20causing%20data%20problems%20when%20I%20extract%20them%20from%20another%20database.%20I%20can%20not%20seem%20to%20find%20a%20way%20to%20make%20them%20all%20the%20same%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22chipwdomi_4-1632165460476.png%22%20style%3D%22width%3A%20232px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311637i94A1726F1AC79651%2Fimage-dimensions%2F232x81%3Fv%3Dv2%22%20width%3D%22232%22%20height%3D%2281%22%20role%3D%22button%22%20title%3D%22chipwdomi_4-1632165460476.png%22%20alt%3D%22chipwdomi_4-1632165460476.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%20%26nbsp%3Bnumber%20in%20cell%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22chipwdomi_7-1632165750559.png%22%20style%3D%22width%3A%20190px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311642i7403DA357013AF3E%2Fimage-dimensions%2F190x88%3Fv%3Dv2%22%20width%3D%22190%22%20height%3D%2288%22%20role%3D%22button%22%20title%3D%22chipwdomi_7-1632165750559.png%22%20alt%3D%22chipwdomi_7-1632165750559.png%22%20%2F%3E%3C%2FSPAN%3Enumber%20in%20formula%20bar%3C%2FP%3E%3CP%3E%26nbsp%3Bthis%20is%20a%20exact%20match%20in%20the%20cell%20and%20in%20the%20formula%20bar%2C%20this%20is%20how%20I%20prefer%20them%2C%20this%20is%20how%20the%20good%20data%20comes%20over%20from%20the%20other%20database%20I%20pull%20from.%20How%20can%20I%20get%20the%201st%20ones%20to%20be%20like%20the%202nd%20ones%20that%20match%20both%20the%20formula%20bar%20and%20the%20excel%20cell%20itself%3F%3C%2FP%3E%3CP%3EI'm%20fixing%20this%20in%20excel%20and%20I%20can%20not%20fix%20as%20we%20get%20the%20data%20sent%20to%20us%20like%20this%2C%20so%20what%20the%20move%20I%20make%20to%20correct%20the%20entire%20column.%20I%20wish%20format%20painter%20would%20do%20it%2C%20but%20no.%26nbsp%3B%20Please%20any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2768342%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2768443%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20formatting%20of%20telephone%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2768443%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1161460%22%20target%3D%22_blank%22%3E%40chipwdomi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20examples%20in%20red%2C%20the%20cell%20value%20is%20a%20%3CSTRONG%3Enumber%3C%2FSTRONG%3E.%20That%20is%20why%20you%20see%20it%20as%20a%20plain%20mumber%20in%20the%20formula%20bar.%20A%20special%20number%20format%20has%20been%20applied%20to%20it%20to%20make%20it%20appear%20as%20a%20phone%20number%20in%20the%20cell%2C%20but%20the%20value%20stored%20in%20the%20workbook%20is%20the%20plain%20number.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20a%20value%20such%20as%20(954)%20731-5100%20to%20be%20the%20stored%20value%2C%20it%20must%20be%20a%20%3CSTRONG%3Etext%20value%3C%2FSTRONG%3E%2C%20not%20a%20number.%3C%2FP%3E%0A%3CP%3EI%20don't%20know%20why%20some%20values%20come%20over%20from%20the%20database%20as%20a%20number%20and%20others%20as%20a%20text%20value.%3C%2FP%3E%0A%3CP%3ETo%20convert%20all%20phone%20numbers%20to%20text%20values%2C%20you%20could%20run%20the%20following%20macro.%20Select%20the%20range%20with%20phone%20numbers%20first.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20ConvertPhoneNumbers()%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20For%20Each%20rng%20In%20Selection%0A%20%20%20%20%20%20%20%20If%20IsNumeric(rng.Value)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20rng.Value%20%3D%20rng.Text%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20rng%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2768444%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20formatting%20of%20telephone%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2768444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1161460%22%20target%3D%22_blank%22%3E%40chipwdomi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20one%20is%20number%20formatted%20with%20brackets%20and%20dashes.%20Second%20one%20is%20text.%20If%20you%20prefer%20texts%20you%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTEXT(A1%2C%22%5B%26lt%3B%3D9999999%5D%23%23%23-%23%23%23%23%3B(%23%23%23)%20%23%23%23-%23%23%23%23%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20convert%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

chipwdomi_8-1632165927985.png

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

chipwdomi_1-1632164445632.png

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

chipwdomi_4-1632165460476.png   number in cell   

chipwdomi_7-1632165750559.pngnumber 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?

 

 

 

4 Replies

@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

@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

@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?

@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.