6

If you type something like a phone number that begins with a 0 into Excel, by default, Excel assumes the user didn't mean what they did, converts the text into a regular number, and removes the leading 0.

There are various workarounds to avoid this, for example, text import settings if opening a file like a CSV, or using custom cell formats, or typing a ' if you're inputting the text manually, etc etc.

What I want, however, is for excel to NEVER remove a leading zero unless I specifically tell it to treat a cell as a number.

I want excel to default to treating 012345 the same way it treats Q12345. If it begins with a 0, assume the user knows what they're doing, and treat it as text, without changing it (no matter whether it was typed manually, copied in, imported etc etc, everywhere on all workbooks, by default).

Is this possible?

Everything I can find about this is along the lines of "how to keep leading zeros in X specific context" - I'm looking for something that always keeps leading zeros, in every cell of every workbook on my copy of Excel, unless I tell it to format a particular cell as a number.

(I'm using Excel 2007 but if this is possible in a more recent version, I might upgrade for it)

Community
  • 1
user56reinstatemonica8
  • 5,494

1 Answers1

4

Sorry to be the bearer of bad news, but it's not currently possible based on the answer from a Microsoft employee in this thread.

I've checked and can confirm that there's no option for disabling auto-formatting of numbers in either the current release, Excel 2013 or in the Office 2016 preview. You can disable auto-formatting in some other cases e.g. replacing URLs with hyperlinks.

There's the chance that such an option may be introduced in a future version, but given the annoyance that this 'feature' has caused to users (including me) over many versions I rather doubt it.

Tinkerer
  • 118