There's a quiet cruelty in Excel's number formatting, and it captures this perfectly. The user did everything right: copied values, applied a custom format, watched most of the data fall into place. Then the leading zeros turned on them. One click in the formula bar, one press of Enter, and 00781202001 became 0007-8120-20. The format didn't fail. The data did. And that's the real lesson here.
The problem isn't the format. It's that Excel sees a number where the user sees an identifier. When you type 00781202001 into a cell set to General, Excel strips the leading zeros and stores a numeric value. The custom format 00000-0000-00 is just a mask. It displays hyphens and padded digits, but the underlying value remains a number. The moment you edit that cell, Excel reapplies its own logic, and the mask shifts. The user's option to automatically insert a decimal point only compounds the confusion, because Excel is quietly assuming every entry is a decimal waiting to happen.
The practical fix is to stop treating these as numbers altogether. Convert the values to text before applying any format, or use a formula to insert the hyphens directly into a text string. Something like =TEXT(A1,"00000-0000-00") will work if the cell is already text, but the safer route is to use =TEXT(A1,"0000-0000-00") on a copy of the data and then paste those results as values. That way, the hyphens are part of the text, not a cosmetic layer on top of a number. No editing surprises, no silent reformatting, no decimal point interference. The user's instinct to preserve the original digits is correct; the tool just needs to be told that those digits are not numbers.
This is the kind of frustration that makes people abandon spreadsheets for something more forgiving. But the answer isn't to ditch Excel. It's to understand that formatting and data type are two different things. The moment you stop expecting Excel to read your mind and start explicitly defining whether a value is text or a number, half your formatting headaches disappear. If you're dealing with IDs, SKUs, phone numbers, or anything else where leading zeros matter, make them text from the start. Do that, and you can format with confidence, because the data you see is the data you actually have.