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

14 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
  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
  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. Spy landline phone recorder is a gadget that is utilized for recording of a wide range of approaching and active calls. 0800 call forwarding

    ReplyDelete
  11. 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
  12. Why at that point do we have to hold tight to our landline telephone? In the event that you have a broadband bundle in your home then you will in any case require a landline and it merits examining the rates since calls can be aggressively evaluated, especially when consistently calling another landline number.cost of 0800 numbers

    ReplyDelete