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

74 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
  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

    ReplyDelete
    Replies
    1. Hi If you want to add +91 in phone number then: In Column A Paste Contact Numbers and in Column B paste +91 in each row parallel to contact number.
      Then in column C user this formula =(A1&B1)
      You get result. Copy the data from Column C and Past as special "text" in column D
      You get your required result.

      Delete
  4. hey

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

    Just type in this format '+91 1234567890

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

      Delete
  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

    ReplyDelete
    Replies
    1. Hi If you want to add +91 in phone number then: In Column A Paste Contact Numbers and in Column B paste +91 in each row parallel to contact number.
      Then in column C user this formula =(A1&B1)
      You get result. Copy the data from Column C and Past as special "text" in column D
      You get your required result.

      Delete
  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

    ReplyDelete
    Replies
    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

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

    111-111-0001
    111-111-0002
    111-111-0003
    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.

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

    ReplyDelete



  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

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

    ReplyDelete
  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

    ReplyDelete
  15. Fascinating. Definitely bookmarking this page. Really valuable info, thanks for posting.
    www.telephonecodes.org

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  18. Tidak ada yang lebih menarik dibanding dengan permainan poker dengan online. permainan ini telah diketahui lama menjadi permainan menguntungkan
    asikqq
    dewaqq
    sumoqq
    interqq
    pionpoker
    bandar ceme terpercaya
    hobiqq
    paito warna
    forum prediksi

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

    ReplyDelete
  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

    ReplyDelete
  21. Thank you for excellent article.You made an article that is interesting.
    AWS Solutions Architect courses in Bangalore with certifications.
    https://onlineidealab.com/aws-training-in-bangalore/


    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  26. Such a wonderful article and I feel that it is best to write more on this topic. Thank you so much because i learn a lot of ideas about it. Keep posting...
    Digital Marketing Course In Kolkata
    Web Design Course In Kolkata
    SEO Course In Kolkata

    ReplyDelete
  27. Right here is the right website for anyone who wants to understand this topic. You realize so much its almost tough to argue with you (not that I really will need to…HaHa). You certainly put a new spin on a subject which has been discussed for Read more decades. Excellent stuff, just great!

    ReplyDelete
  28. This was really one of my favorite website. Please keep on posting. ExcelR Digital Marketing Class In Pune

    ReplyDelete
  29. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    Data Science Certification in Bangalore

    ReplyDelete
  30. You might comment on the order system of the blog. You should chat it's splendid. Your blog audit would swell up your visitors. I was very pleased to find this site.I wanted to thank you for this great read!!
    Data Science Course in Bangalore

    ReplyDelete
  31. The content that I normally see is nothing like what you have written. This is very well-thought out and well-planned. You are a unique thinker and bring up great individualized points. Please continue your work.
    Best Data Science training in Mumbai

    Data Science training in Mumbai

    ReplyDelete
  32. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

    ReplyDelete
  33. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    Ethical Hacking Course in Bangalore
    Certified Ethical Hacker Course

    ReplyDelete
  34. Wow! Such an amazing and helpful post this is. I really really love it. I hope that you continue to do your work like this in the future also.
    Ethical Hacking Training in Bangalore
    Ethical Hacking Training

    ReplyDelete
  35. Thumbs up guys your doing a really good job. It is the intent to provide valuable information and best practices, including an understanding of the regulatory process.
    Cyber Security Course in Bangalore

    ReplyDelete
  36. Very nice blog and articles. I am really very happy to visit your blog. Now I am found which I actually want. I check your blog everyday and try to learn something from your blog. Thank you and waiting for your new post.
    Cyber Security Training in Bangalore

    ReplyDelete
  37. I will really appreciate the writer's choice for choosing this excellent article appropriate to my matter. Here is deep description about the article matter which helped me more.
    Best Institute for Cyber Security in Bangalore

    ReplyDelete
  38. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
    Data Science Training Institute in Bangalore

    ReplyDelete
  39. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
    Best Data Science Courses in Bangalore

    ReplyDelete
  40. 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.
    360DigiTMG

    ReplyDelete
  41. I’m happy I located this blog! From time to time, students want to cognitive the keys of productive literary essays composing. Your first-class knowledge about this good post can become a proper basis for such people. nice one
    360DigiTMG

    ReplyDelete
  42. Amazing post found to be very impressive while going through this post. Thanks for sharing and keep posting such an informative content.

    360DigiTMG Cyber Security Course

    ReplyDelete
  43. Its content is brilliant in many ways. I think it is an attractive and revealing material. Thank you very much for caring about your content and your readers.

    360DigiTMG Data Science Courses

    ReplyDelete
  44. I have read your article, it is very informative and useful to me, I admire the valuable information you offer in your articles. Thanks for posting it ...

    360DigiTMG Data Science Certification

    ReplyDelete
  45. This was not only extraordinary in truth this was truly ideal your ability recorded as a hard copy was incredible.
    ai training in noida

    ReplyDelete
  46. Truly mind blowing blog went amazed with the subject they have developed the content. These kind of posts really helpful to gain the knowledge of unknown things which surely triggers to motivate and learn the new innovative contents. Hope you deliver the similar successive contents forthcoming as well.

    360DigiTMG Machine Learning Course

    ReplyDelete
  47. Wonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such an amazing content for all the curious readers who are very keen of being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in future too.

    360DigiTMG Tableau Course

    ReplyDelete