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
0091 00000000000091 9971112814


  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.

  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?

  3. Hey , Suppose there is a contact number 9999999999 and i want to add +91 before it ...how would you do that ? Plz reply soon! thnx

  4. hey

    if we want to type phone number eg:+91 1234567890

    Just type in this format '+91 1234567890

    1. Classic! When pasted it only shows number not apostrophe. Thanks!

  5. Hey , Suppose there is a contact number 9999999999 and i want to add +91 before it ...how would you do that ? Plz reply soon! thnx

  6. Hey Guys here is the solution: follow the steps
    1. Right Click on the Cell
    2. Select "Format Sells"
    3. Select "Custom" from left end
    4. Add + Sign in front of "Type" (you will see something like this in type section 0;[Red]0 add a + sing before it. Once you add + it should look like this +0;[Red]0 )

    Select all the cells and do same once you learned it.

    Cheers! hope it helped.

    @Mathew - Microsoft Certified Excel Professional

    1. i have contact number starts with +91........ so i ccopy it into excel bt it cant show + infront of 91..... when i save it or convert it into vcf... help me

  7. Hi, i have telephone numbers below... i would like to get the ranges calculated:

    and so on...

    if i would like to get the ranges for instance 111-111-0001 to 0100 in column B. What formula to be used. Please advise.

  8. Keep up the good work , I read few posts on this web site and I conceive that your blog is very interesting and has sets of fantastic information.
    0800 call forwarding

  9. These things may sound ridiculous, the next time you dig into the many aspects of it would appear logical. Article is truely awfully impressive how to get help in windows 10

  10. Voip that is Voice over Internet convention has turned out to be to a great degree prominent among numerous enterprises that have their business everywhere throughout the globe. DCS Telecom

  11. I entered a no in excel form by when I hit enter last 3 digits changed


  12. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.

    Php course in chennai

  13. It is full of informative stuff thanks for sharing. Keep sharing information like this. extract phone numbers from outlook

  14. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. ukraynaca

  15. Fascinating. Definitely bookmarking this page. Really valuable info, thanks for posting.

  16. This is my first time visit to your blog and I am very interested in the articles that you serve. Provide enough knowledge for me. Thank you for sharing useful and don't forget, keep sharing useful info: www.localisertel.com

  17. This is a good post. This post gives truly quality information. I’m definitely going to look into it. Really very useful tips are provided here. Thank you so much. Keep up the good works como espiar un celular

  18. Tidak ada yang lebih menarik dibanding dengan permainan poker dengan online. permainan ini telah diketahui lama menjadi permainan menguntungkan
    bandar ceme terpercaya
    paito warna
    forum prediksi

  19. This is the first & best article to make me satisfied by presenting good content. I feel so happy and delighted.By Learn Digital Marketing Course Training in Chennai it will help to get Digital Marketing Training with Placement Institute in Chennai. If you Learn Social Media Marketing Training with Placement Institute in Chennai, you will get job soon.

    Learn Best Digital Marketing Course Training in Chennai in professional institute to get reputed job.

  20. Awesome article, it was exceptionally helpful! I simply began in this and I'm becoming more acquainted with it better! Cheers, keep doing awesome! Phone Trace

  21. Thank you for excellent article.You made an article that is interesting.
    AWS Solutions Architect courses in Bangalore with certifications.

  22. I wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post. legal entity identifier

  23. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

  24. keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore | https://www.excelr.com/digital-marketing-training-in-bangalore

  25. Good job! Fruitful article. I like this very much. It is very useful for my research. It shows your interest in this topic very well. I hope you will post some more information about the software. Please keep sharing!!
    SEO Training in Chennai
    SEO Training in Bangalore
    SEO Training in Coimbatore
    SEO Training in Madurai
    SEO Course in Chennai
    SEO Course in Chennai
    SEO Course in Bangalore
    SEO Course in Coimbatore