Groups

    The Number Zero won't display when entered into a cell (excel)

    I am working within a excel spread
    When I enter the number "0" at the beginning it will not show. An example is 01267589658 but once inputted it shows as 1267589658?

    I have opened a new spread sheet - no problem. I have checked font colors,
    checked using the number and general options - this doesn't resolve the
    issue.

    Suggestions

    Thanks

    19 Comments

    Banned

    You have to change the format...right click on the cell and change the format to custom and pick the correct option..forget the one it is and I dont have Excel loaded on home PC...so cant remember the exact one for you to pick

    format cells > custom > type = 0

    I think.

    Try typing a ' first ie '01234

    I'm sure this will work :thumbsup:

    Try prefixing it with '
    Perhaps?

    Right click in cell
    Format cells
    Text


    It will now let you enter the leading zero but will be left justified

    Original Poster

    emasu;3616838

    format cells > custom > type = 0I think.



    Hmmm odb and emasu tried this but still the same. So i tried text instead and it works. Do you think ill have any problems importing it to access?

    Original Poster

    choc1969;3616915

    Right click in cellFormat cellsTextIt will now let you enter the leading … Right click in cellFormat cellsTextIt will now let you enter the leading zero but will be left justified



    yep just tried that :thumbsup: but u reckon ill get any problems when importing it in access.

    MarzBarz;3616946

    yep just tried that :thumbsup: but u reckon ill get any problems when … yep just tried that :thumbsup: but u reckon ill get any problems when importing it in access.



    shouldn't be a problem, you can change the imported format in access of it does but I've never experienced it :thumbsup:

    Will most probably be a problem exporting to most things if you use the ' method.

    The correct way is to fix the length of input, go to format cells/custom then click the "0" option and type in "00000" to enable 00123 for example.

    Original Poster

    ok thanks repped all

    Banned

    pghstochaj;3617000

    Will most probably be a problem exporting to most things if you use the ' … Will most probably be a problem exporting to most things if you use the ' method.The correct way is to fix the length of input, go to format cells/custom then click the "0" option and type in "00000" to enable 00123 for example.




    There is defo another way the problem with that method is you are set to a specific amount of characters...wheras the other way you can do anything...



    Sorry Marz lad..if I was at work with it in front of me I could answer you in 30 seconds...I do this stuff everyday I just dont have it at home

    Setting to text should work if your just inputting the numbers by hand

    Banned

    I thought it was a case of entering # in the cell formatting so that it always displays at least one character. (Or #.# if you want it to have a single decimal place)

    Banned

    Titchimp;3617126

    Setting to text should work if your just inputting the numbers by hand



    Only problem with that (and I agree it would do it) is that you couldnt then use the cell content to add totals etc or use formulas.

    Banned

    guv;3617174

    Only problem with that (and I agree it would do it) is that you couldnt … Only problem with that (and I agree it would do it) is that you couldnt then use the cell content to add totals etc or use formulas.



    yup wont work with certain things

    put a full stop at the beginning, dont know why it works but it does. keeps the 0 at the start and the full stop disappears

    ODB_69;3617199

    yup wont work with certain things



    Of course it would, loads of ways, including value(x) or RIGHT(x) and so on.

    question is why do you want to do this, if u want to do it for a label then text will do, if you want to perform math on this cell then it's not advised.
    but then again if you want to perform math on this cell why would you want to show a 0

    Original Poster

    Im inputting phone numbers lol. Ive done it now by the text method and used a random excel number generator to fill it in.
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Cheap northface alternatives?11
      2. NUS EXTRA - Cheap (Legitimate Way) - 3 year card?610
      3. What to do?????56
      4. Tradesmen battery power tools.11

      See more discussions