Tuesday, October 27, 2009

Excel Number Format : Telephone numbers with leading + sign

This is in continuation of my earlier post Custom Formating - Excel Number Format

Excel does not display the leading + sign entered by users, similarly the leading zeros are also not recoganised by excel. However custom formating trick can help you do this. Refer to the following table, where in Phone number 9971112814 has been formated differently to display different formats for the same cell contents.

91 is country code for India, you can change it to whatever code you want to display the number accordingly.


FormatDisplayed as
+91 0000000000+91 9971112814
+91(0)0000000000+91(0)9971112814
+91(0)000-000-0000+91(0)997-111-2814
0091 00000000000091 9971112814
Posted by Yogesh Gupta at 9:37 PM
Labels: , ,
Reactions: 

2 comments:

  1. I have a large data base with telephone numbers, in a single column. I wish to add the number 1 to all the telephone numbers in the cells. When I record the steps, it copies the previous telephone.
    ReplyDelete
  2. Your question is not entirely clear to me. Do you have the numbers formatted (using Cell Format) in any way? If so, what is the format? If not, are the numbers constructed as text with the formatting hard coded into your numbers? Also, you say you recorded the steps... does that mean you are looking for a VB solution to physically add the "1" in front of your existing number as opposed to displaying the "1" via a custom format in the manner Yogesh outlined in this article?
    ReplyDelete

Subscribe to: Post Comments (Atom)