Share via

I want Excel to stop mucking with my data Automagically - Stop Removing Leading Zeros

Anonymous
2022-03-13T14:59:17+00:00

If I enter a number in a field with leading zeros, It's because I want the field to have leading zeros. Stop removing my leading zeros.

No, I don't wan to have to set the field to text every stupid time.

And there are situations where I get spreadsheets from clients who "don't know no better" and the leading zeros are gone. This is idiotic. I can't fix their serial numbers or IMEI numbers if the leading zeros are gone before I even get the doc.

Stop messing with people's data. If they enter a leading zero, leave it unless they tell you otherwise.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-13T18:57:47+00:00

    I'm sorry man.

    Thanks for the reply.

    But you don't get it.

    The default behavior is dumb. It breaks people's data. There is no value in removing leading zeros.

    And I am not the originator of the broken spreadsheets. I am downstream from some normie user who doesn't know anybetter.

    Stop breaking people's data.

    80+ people found this answer helpful.
    0 comments
  2. Anonymous
    2022-03-13T19:45:16+00:00

    We can't do anything, it is a feature of Excel. Leading zeros on numbers serve no mathematical function.

    Leading zeros on numbers may be necessary for display/output formatting, but that is not math.

    .

    stripping leading zeros is a fundamental assumption in Excel. You can complain about it, but MS is not about to change it.

    .

    This video shows a couple of tips for dealing with leading zeros

    Bill Jelen Excel Oddities / Tricks Webinar

    https://www.youtube.com/watch?v=H7rSetB7IRo

    Bill Jelen is the host of mrexcel.com and the author of 65 books about Microsoft Excel including Excel Gurus Gone Wild, Pivot Table Data Crunching, and Excel 2019 Inside Out.

    APPROXIMATE CONTENT TIMESTAMPS

    0:58:30 - Leading Zeroes with BASE

    Leading zeros using Text() function

    =Text(value,”0000000”)

    3 people found this answer helpful.
    0 comments
  3. Anonymous
    2022-03-13T15:37:28+00:00

    Hello Patrick,

    AFAIK, if you want Excel to stop removing the leading zeros before the number, we need to set a cell as a Text format or use the Custom formating. Since you mentioned "I don't want to have to set the field to text", if the number of digits in IMEI numbers are constant, you can set a custom format to all the cells to show the leading zeros in the cells.

    In addition, I will keep this thread open, so Excel experts can share their views and ideas on your requirement.

    Best Regards,
    Chitrahaas

    0 comments