Home

Phone Number Format

%3CLINGO-SUB%20id%3D%22lingo-sub-891970%22%20slang%3D%22en-US%22%3EPhone%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-891970%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20I%20am%20new%20to%20the%20forum%20but%20have%20been%20an%20Excel%20user%20for%20many%20years.%20On%20previous%20versions%20it%20was%20so%20easy%20to%20format%20all%20phone%20numbers%20by%20selecting%20the%20cells%20and%20changing%20the%20format%20to%20%22Special%22%20and%20%22Phone%20Number%22.%20This%20gave%20me%20the%20desired%20outcome%20of%26nbsp%3B%20(%23%23%23)%20%23%23%23-%23%23%23%23%3C%2FP%3E%3CP%3ENow%2C%20it%20doesn't%20change%20them%20at%20all.%20The%20numbers%20that%20start%20in%20that%20format%20stay%20that%20way%20but%20the%20ones%20in%20the%20international%20format%20do%20not%20change%20to%20US%20(or%20what%20I%20assume%20is%20the%20US%20format%2C%20shown%20above).%20Please%20help!%3CBR%20%2F%3ECurrently%2C%20if%20the%20number%20is%20originally%20in%20international%20format%2C%20it%20does%20not%20give%20me%20the%20option%20under%20%22Special%22%20to%20change%20it.%20For%20example%2C%20cells%20that%20are%20as%20follows%3A%20%22%2B1%208129450000%20ext%3A%22%20then%20when%20I%20select%20Format%20and%20Special%2C%20this%20is%20what%20I%20see%20under%20Phone%20Number.%20If%20it%20is%20in%20the%20US%20format%2C%20that%20is%20what%20it%20shows%20under%20Special%20and%20Phone%20Number.%3CBR%20%2F%3EThank%20you%20in%20advance%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-891970%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892184%22%20slang%3D%22en-US%22%3ERe%3A%20Phone%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892184%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419671%22%20target%3D%22_blank%22%3E%40vasthoagies%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20phone%20number%20formatting%20you're%20talking%20about%20is%20a%20%22number%20format%22.%20It%20applies%20to%20numbers%20only.%20Another%20thing%20to%20note%20about%20number%20formats%20is%20that%20they%20only%20change%20the%20appearance%20of%20the%20value%20of%20the%20cell%3B%20they%20don't%20actually%20change%20the%20underlying%20number.%20For%20example%2C%20when%20you%20type%20in%201234567890%20in%20a%20cell%20and%20then%20format%20the%20number%20as%20a%20phone%20number%2C%20Excel%20displays%20that%20cell%20as%20a%20phone%20number%20while%20the%20underlying%20number%2C%201234567890%2C%20remains%20unchanged.%3CBR%20%2F%3EWhen%20you%20enter%20something%20non-numeric%20in%20a%20cell%2C%20such%20as%20a%20space%2C%20the%20whole%20cell's%20content%20becomes%20a%20text.%20So%20in%20your%20examples%2C%20the%20value%20(%23%23%23)%20%23%23%23-%23%23%23%23%20is%20actually%20a%20text%20string%20(unless%20it%20is%20a%20%22number%22%20being%20displayed%20with%20a%20custom%20number%20format%2C%20of%20course).%20So%20when%20you%20try%20to%20apply%20a%20number%20format%20on%20it%2C%20it%20doesn't%20apply.%3CBR%20%2F%3ESame%20with%20your%20second%20example%3A%20%22%2B1%208129450000%20ext%3A%22.%20The%20cell%20contains%20non-numeric%20characters%20like%20space%20and%20ext%20etc.%20in%20the%20cell%20which%20makes%20it%20a%20text%20string.%20So%20number%20formats%20don't%20apply%20in%20this%20case%20either.%3CBR%20%2F%3EIf%20you%20want%20to%20display%20a%20cell%20in%20the%20phone%20number%20format%2C%20i%20suggest%20you%20enter%20the%20number%20only%20and%20then%20set%20the%20custom%20number%20format%20to%20that%20of%20%22Phone%20number%22.%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20display%20in%20the%20International%20format%2C%20I%20suggest%20you%20enter%20the%20calling%20codes%20in%20one%20column%20and%20the%20phone%20number%20in%20another.%20Then%20you%20can%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fconcatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECONCATENATE%3C%2FA%3E%20function%20to%20join%20the%20text%20strings.%3CBR%20%2F%3EHope%20that%20was%20easy%20to%20follow%20and%20understand.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892192%22%20slang%3D%22en-US%22%3ERe%3A%20Phone%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892192%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419671%22%20target%3D%22_blank%22%3E%40vasthoagies%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20so%20I%20am%20understanding%20correctly%2C%20per%20your%20example%2C%20you%20would%20like%20a%20cell%20containing%26nbsp%3B%3CSPAN%3E%22%2B1%20XXXXXXXXXX%20ext%3A%22%20to%20be%20formatted%20as%20%22%2B1%20(XXX)-XXX-XXXX%20ext%22%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAre%20all%20cells%20formatted%20this%20way%3F%20Are%20some%20formatted%20as%20simply%20%22XXXXXXXXXX%22%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-897502%22%20slang%3D%22en-US%22%3ERe%3A%20Phone%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%20Yes%2C%20that%20is%20what%20I%20am%20trying%20to%20accomplish.%20Some%20are%20in%20that%20format%20already%2C%20others%20are%20the%20international%20format.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-897552%22%20slang%3D%22en-US%22%3ERe%3A%20Phone%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289116%22%20target%3D%22_blank%22%3E%40Takmil%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20the%20response!%20Yes%2C%20all%20of%20the%20cells%20have%20symbols%20to%20make%20them%20text%2C%20which%20is%20fine%20for%20my%20purposes.%20I%20am%20making%20a%20list%20of%20contacts%20to%20print%20(monthly%2C%20it%20is%20our%20client%20birthday%20list)%20so%20as%20far%20as%20that%20goes%2C%20it%20does%20not%20matter%20to%20me%20if%20they%20are%20text%20or%20numbers%2C%20as%20long%20as%20they%20are%20uniform%20and%20easy%20to%20read.%20As%20I%20said%2C%20in%20our%20previous%20version%20of%20Excel%2C%20I%20was%20able%20to%20do%20this%20easily%20but%20I%20guess%20the%20updated%20version%20wants%20to%20assign%20%22phone%20number%22%20to%20whatever%20is%20already%20in%20each%20cell.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
vasthoagies
New Contributor

Hello! I am new to the forum but have been an Excel user for many years. On previous versions it was so easy to format all phone numbers by selecting the cells and changing the format to "Special" and "Phone Number". This gave me the desired outcome of  (###) ###-####

Now, it doesn't change them at all. The numbers that start in that format stay that way but the ones in the international format do not change to US (or what I assume is the US format, shown above). Please help!
Currently, if the number is originally in international format, it does not give me the option under "Special" to change it. For example, cells that are as follows: "+1 8129450000 ext:" then when I select Format and Special, this is what I see under Phone Number. If it is in the US format, that is what it shows under Special and Phone Number.
Thank you in advance for your help!

4 Replies

@vasthoagies 

The phone number formatting you're talking about is a "number format". It applies to numbers only. Another thing to note about number formats is that they only change the appearance of the value of the cell; they don't actually change the underlying number. For example, when you type in 1234567890 in a cell and then format the number as a phone number, Excel displays that cell as a phone number while the underlying number, 1234567890, remains unchanged.
When you enter something non-numeric in a cell, such as a space, the whole cell's content becomes a text. So in your examples, the value (###) ###-#### is actually a text string (unless it is a "number" being displayed with a custom number format, of course). So when you try to apply a number format on it, it doesn't apply.
Same with your second example: "+1 8129450000 ext:". The cell contains non-numeric characters like space and ext etc. in the cell which makes it a text string. So number formats don't apply in this case either.
If you want to display a cell in the phone number format, i suggest you enter the number only and then set the custom number format to that of "Phone number".

If you want to display in the International format, I suggest you enter the calling codes in one column and the phone number in another. Then you can use the CONCATENATE function to join the text strings.
Hope that was easy to follow and understand.

Hello @vasthoagies,

 

Just so I am understanding correctly, per your example, you would like a cell containing "+1 XXXXXXXXXX ext:" to be formatted as "+1 (XXX)-XXX-XXXX ext"?

 

Are all cells formatted this way? Are some formatted as simply "XXXXXXXXXX"?

@PReagan  Yes, that is what I am trying to accomplish. Some are in that format already, others are the international format. Thanks.

@Takmil  Thank you for the response! Yes, all of the cells have symbols to make them text, which is fine for my purposes. I am making a list of contacts to print (monthly, it is our client birthday list) so as far as that goes, it does not matter to me if they are text or numbers, as long as they are uniform and easy to read. As I said, in our previous version of Excel, I was able to do this easily but I guess the updated version wants to assign "phone number" to whatever is already in each cell. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies