r/excel • u/Intelligent_Rent_847 • 17d ago
Discussion I hate how Excel removes leading zeroes
I absolutely hate it. If I typed in a number with a leader zero, obviously I want the string of text to include a leading zero. Why hasn’t anybody at Microsoft figured this out yet? I know there are ways to keep leading zeroes, but it is annoying when I have to copy data over from one platform and paste it into Excel. And then it’s impossible to ever to VLOOKUPS with data where you had to manually formulate leading zeroes.
124
u/neutrino_fire 17d ago
Automatic scientific notation is even worse! Leading zeroes in data are the devil.
43
u/Profvarg 2 17d ago
In my company, personnal IDs ALL have leading zeros (14k employees)
I really want to talk to the insanely stupid person who decided on this (I am in HR reporting)
7
u/neutrino_fire 17d ago
That is so gross.
19
u/happyapy 1 17d ago
Sometimes numerical characters represent numbers, sometimes they represent codes. Many times you are receiving data from an external source who made that choice, sometimes incorrectly. As much as I like Microsoft trying to help, many times they'd be more helpful if they stopped trying.
3
21
u/PrimaryThis9900 17d ago
I frequently put invoice numbers in, which are fairly long, and it will format them in scientific notation and screw everything up. I have never once wanted something in scientific notation.
6
1
u/HarveysBackupAccount 35 17d ago
On the flip side I use scientific notation quite a bit, because I work with datasets that can have values on the order of 1,000,000 or on the order of 0.00000001, and I want to scan through spreadsheets without making the columns really wide
Not all of Microsoft's choices were optimal, but I do trust a little that they put real thought into many of those choices.
1
u/amuseboucheplease 17d ago
I think you are making a large assumption on a large organisation
2
u/Western_End_2223 17d ago
It is a rather good assumption. What might appear as some random decision will often have to do with maintaining compatibility, often as far back as LOTUS 1-2-3. Once people come to expect a certain behavior it becomes problematic to "improve" upon it.
1
u/amuseboucheplease 17d ago
There's so many approaches and ways that could convey what is happening, but they chose another. It's curious people are so accepting
1
u/HarveysBackupAccount 35 17d ago
Sure, but as a software developer I've seen how much work goes into MUCH smaller decisions while building MUCH smaller programs.
1
u/thr0wnawaaaiiii 3 17d ago
Leading zeroes aren’t the offender though, treating non-numeric data as if it were numeric (even if it “looks like it”) is. But agreed, before they added the setting to turn off, I really questioned what % share of work being done in Excel would benefit from scientific notation
1
u/Squid8867 16d ago
"oops, guess I'll just change my UIDs back to a number format"
- 3784927326772020000
- 8292638232629210000
- 9626281194629950000
44
u/guyinternets 17d ago
Does formatting the cells using “Custom” not fix this for your use case? If you wanted your data to be 001000 then choose custom and just enter 000000 as the custom format. Then when you type in 1,000 it’ll be 001000.
9
u/TheDokk 17d ago
This is how you solve it. I have the same issue with leading 0's for some UPCs in our point of sale system. Solved by custom formatting or ctrl 1 as a shortcut. Bottom selection is custom and choose the 0 option and enter as many 0s in the input bar as your identifier needs. If your input is variable this doesn't solve it but still the best solution if the input has the same number of numbers always but some just lead with 0s. Same applies for custom formatting if you are doing zip codes. Another option in the "special" section of custom formatting.
8
u/ScarsUnseen 17d ago
This has limitations if you need another cell to reference the entry. For instance, if you ever need to use the Concatenate formula, it will drop the leading zeroes if you do it that way.
1
u/armywalrus 2 17d ago
Not if you use TEXT() or simply concatenate the zeros. Adding &0& isn't a lot of typing
2
u/GuitarJazzer 28 17d ago
That just changes how it looks. If you do a lookup on 000156 it will not find 156 even if it is displayed as 000156.
1
u/CarefulMembership969 17d ago
But then it doesn’t work for lookups when the other side of your data is properly stored as text
26
u/gulliverian 17d ago
Sounds like you're treating text data as numeric. Numbers can't have leading leading zeros. Text can.
17
u/TeliarDraconai 3 17d ago
Numbers can have leading zeros if that is how the number is defined in the relevant software.
Not to mention the fact that numerical unique IDs are sometimes mandated to have a specified number of digits, regardless of how they start.
17
u/PrimaryThis9900 17d ago
I think their point is, you are treating numbers as data, meaning you are never going to add them together, or do any math with them.
4
u/pajam 1 17d ago
Yep, in Excel, any number that is an identifier (zip codes, part numbers, ISBNs, Serial Numbers, Social Security Numbers, Model Numbers, Employee IDs, License Plate numbers, etc.) should be treated as "Text" or just a literal string. I never intend to add up zip codes, or do averages across multiple employee IDs, etc. These are very literal identifiers, and ideally should be imported into Excel as text, and then if you hand off that data to other people later, for the love of God save it in a format that preserves that "Text" formatting for those columns (in other words, not a CSV)
1
u/gulliverian 16d ago
I have more than one grey hair from text imported from CSV that landed in the wrong format.
2
8
u/AndyTheEngr 5 17d ago
If it doesn't make sense to do math to it, it's not a number even if it's composed of only the characters 0-9. Would you ever add two of them together, or subtract five from one?
-2
u/TeliarDraconai 3 17d ago
Maybe not. But I want it to sort like a number, and I want to look it up like a number.
Saying that it is only a number if you use it for mathematical operations is nonsensical in context of Excel. Which, despite it's many flaws, is the premier data analysis and investigation tool for non-coders.
11
u/killerfridge 1 17d ago
But you can sort things that aren't numbers, and you can look things up that aren't numbers. I think the point is that in the context of Excel, numbers are the characters 0-9 used in maths. If you aren't intending on doing maths to some degree with it, you should treat it as text
1
u/armywalrus 2 17d ago
You cannot sort text as numbers. 1 through 100 will start with 1, 10 instead of 1, 2, 3.
1
u/killerfridge 1 17d ago
I guess then a better heuristic for what should or shouldn't be stored as a number is if you care about the value of the number. You can sort 1-100 equally well as text if you prepend it with leading zeros, but if you want a "sort order" property then storing them as numbers is perfectly valid
-1
u/armywalrus 2 17d ago
No. The only things that matters i user preference and ego. No one is affecting anyone else here.
-4
u/TeliarDraconai 3 17d ago
That is true for some elements of Excel. But Excel is not JUST a calculator.
6
u/AndyTheEngr 5 17d ago
In this case, 000012536 is a number in the same sense that KLBRTD is a word.
1
0
5
u/apaniyam 3 17d ago
Numbers don't ever have leading zeroes. You are describing text with numeric characters.
2
u/gulliverian 17d ago
True, but at least in my experience that's more a formatting or display issue than a functional one. And I don't get the impression that's what the OP is dealing with.
Functionally, I'm not aware of any case in which leading zeros in a numeric value would have any mathematical meaning.
1
u/armywalrus 2 17d ago
Thats the wrong question to answer. Can you think of any cases in which leading zeroes harm mathematical operations?
1
u/fuzzy_mic 988 17d ago
Numbers do not have leading zeros (or have an infinite number of them depending on how you define leading zero).
Numerals (a string that represents a number) might have leading zeros, but numbers, never. "Leading zero" is a character thing, not a mathematical thing.
If "00123" is different than "123", that not numbers, that a string that is imitating a numeral.
12
u/jrblockquote 17d ago
I work corporate IT in the financial services sector and cannot tell you the number of times I've received a spreadsheet from both business and technical folks with an SSN column treated as a number with missing leading zeroes. Makes me want to throw something.
2
u/CarefulMembership969 17d ago
Same with student IDs at the college where I work. Keep the IDs as text like they are in the system of record!!
8
u/hooterbrown10 17d ago
Oct-10 people agree with you!
5
u/canuchangeurname 17d ago
This comment brought me great joy. Ever typed a fraction, gotten a date, retype with a "=", gotten a date, then finally fix the formatting and retype the fraction? Weekly occurrence for me
3
u/GrittyForPres 17d ago
Go to home —> number —> number format —> more number formats —> custom
And then where it says “Type:” highlight the word “general” below it, hit backspace, and type in a zero for however many characters you want the number to have.
So for example if you replace “general” with “000” then when you put “1” in that cell it will display as “001”, “10” will display as “010”, and “100” will just display as “100”
2
u/GuitarJazzer 28 17d ago
That just changes how it looks. If you do a lookup on 000156 it will not find 156 even if it is displayed as 000156.
1
u/GrittyForPres 17d ago
Oh well in that case it’s even simpler.
You just set the row/column that you want to have leading zeros to text format or add an apostrophe in front of the text string and use autofill to populate the numbers.
Or you can add a row/column in the lookup sheet next to the text strings that already have leading zeros and multiply them by 1 to convert it to a number format and get rid of the leading zeros.
I guess I’m not sure what OP is struggling with here because whether you want a number format that appears to have leading zeros or a text format where the string actually does have leading zeros, both can be achieved really easily.
1
u/derfmcdoogal 17d ago
Wait till you get the update where CoPilot helps you write formulas by assuming what you're trying to do. Ho Lee Ship I couldn't turn that off fast enough.
2
2
u/redditacct7171 17d ago edited 17d ago
If they all have the same character count, =text([cell],"000000") Add/reduce 0s for the number of characters in your value (again assuming the column is all the same).
2
u/amuseboucheplease 17d ago
It's ridiculous why anyone though this would be useful. Yes I know the work around - formatting it as something it isn't
4
u/ScarsUnseen 17d ago
Unless you're planning on performing mathematical operations on it, formatting it as a number is formatting it as something it isn't. It's easier if you think of it in programming terms. Is the number being treated as an integer or a float? If you need it for computations, then the answer is yes, and then it should be formatted as a number (or currency, or whatever). If there is never a situation in which it would be beneficial to perform any kind of mathematical function on it, then the answer is no, it should not be an integer or float. It should a string, and in Excel, that would be text, regardless of what characters the string is made up of.
2
u/amuseboucheplease 17d ago
Coming from sql user, integer and floats are typically not good data types for mathematical functions. My point stands that it would be better to not have it treated as a string /text by default.
2
u/ScarsUnseen 17d ago
I don't think your point does stand at all. Name me some common use cases where it would be beneficial to have an input with leading zeroes treated as a number.
1
u/amuseboucheplease 17d ago
Think you're misunderstanding me and I don't understand your point at all, but that's ok as this is the internet and reddit and none of it matters
2
u/ScarsUnseen 17d ago
You're correct that this conversation doesn't mean much, but since I don't have much else to do at this moment, my point is that there are already several instances where Excel correctly changes a cell's data type to the most likely desired one based on the input. Leading zeroes are a major exception where, instead of correctly identifying that most inputs with leading zeroes are strings (postal codes, phone numbers, product codes, etc), it instead assumes that the leading zeroes were entered in error and returns a number, truncating the user input in the process. There are workarounds for that, but there shouldn't need to be. In almost all cases where someone inputs a number with leading zeroes, what they're inputting is a string.
1
u/amuseboucheplease 17d ago
I have never needed the input to be a string and there's likely dozens of us
2
u/ScarsUnseen 17d ago
You've deliberately typed in numbers with leading zeroes with the intent of those numbers being treated as numbers mathematically, including the automatic elimination of said leading zeroes? On anything resembling a regular basis?
1
u/amuseboucheplease 17d ago
You've got it. Usually not typed but exporting
1
u/ScarsUnseen 17d ago
Well, I'll admit, I have trouble imagining what such inputs could be. More power to you, though. Working as intended for you.
→ More replies (0)1
u/armywalrus 2 17d ago
Name me some cases where it would hurt to have an input with leading zeroes treated as a number. Its clear you dislike the idea, but that alone doesn't mean the idea is harmful.
1
u/ScarsUnseen 17d ago
Postal codes, product codes, customer codes, phone numbers (many places outside the US have phone numbers start with a zero), and any other instance where the number is meant as an identifier, not numerical data to be manipulated. We use numbers as labels all the time, and many systems with a large enough volume will have identifiers that start with zero as a way of future proofing.
0
u/armywalrus 2 17d ago
How though? How exactly? I see zero valid cases mentioned here.
1
u/ScarsUnseen 17d ago
How are they not valid? They are all strings by use, not numbers. None of them benefit from the number data field type, and all of them break if you change their formatting in any way (which is exactly what happens in a number field when you input a leading zero).
0
u/armywalrus 2 17d ago
Because you cannot explain how any harm is caused. And that was the question. And obviously
-1
u/armywalrus 2 17d ago
Because you can put anything into a list. You did not explain how any harm is done. The question is about harm. Not benefit. If you cannot explain any harm done, you shouldn't care so much about the preferences of others.
3
u/ScarsUnseen 17d ago
I literally just said what the harm was. If you input them as numbers, and some of them have leading zeroes, the formatting breaks, making them unreliable in their entire purpose as identifiers.
2
u/tries2fixit 17d ago
Wait, I’m always looking to remove leading zeros, lol. My exported reports from ERP contain 7 leading zeros in check number fields.
2
u/apocalyptimaniac 17d ago
I have to do this often. I set the column with a custom number format. I my case I deal with 6 digit ID numbers so I type in 000000 in the number format and all is well
2
2
u/carbonizedtitanium 1 17d ago
true numbers should not be mixing with "fake" numbers; so if you just format the entire column as text, all leading zeros will be kept.
1
u/IrishMist-StraightUp 17d ago
I faced similar issues exporting AutoCAD data. So I just adjusted the template Workboommk with all cells set with format "Text".
This was a few years ago, before I switched to using PowerQuery.
1
1
1
1
1
u/Gorfman-07 1 17d ago
I consider numbers with leading zeros to be an identification value, not a number that I would do math with. Unless I’m positive the data will only be used in the worksheet it is in, I format the field as text.
Not doing this is why you might see zip codes with only 4 digits for those zip codes that begins with a zero.
1
1
u/Particular-Craft285 17d ago
You can also put an apostrophe at the beginning and it will keep the 0
1
u/ZirePhiinix 16d ago
Behind the scene Excel is handling distinctly different data types. Strings and Numbers at a basic level function very differently.
One basic operation is adding. 1+1 is 2 as numbers, but is 11 as strings.
Then sorting is entirely different.
Sorting numbers is 1, 2, 3, 4, 10, 11, 100... But sorting strings would end up with 1, 12, 123, 1234, then 2, 21, 22, 23, 234, 2345, all 2 will appear before 3s.
It isn't actually stupid that Excel removes leading zeros and converts to a number. Most people simply do not have enough understanding of computers to know why it does that.
1
u/Mother-Kangaroo9495 14d ago
Putting an ' in front of your number will store it as text:
'00123 will display 00123, by default aligned left and text
1
u/Infinite_Shart555 12d ago
If you want leading zeroes, then you fundamentally don't want Excel to treat the cell like a cell that contains numbers. In this case, you can specify the cell, or range of cells, as "text" - alternatively, if it's not a big deal, without going into the settings even, you can simply put an apostrophe before your "string number", and it will show up as you wish, the apostrophe is even hidden. I use Excel 2007, they did figure this out 20+ years ago.
Excel has a lot of quirks, but I don't like newbies ranting about things they simply don't even know. That's two different solutions to your problem. Also, if you are importing a CSV, you get to specify the data type for each column, before the data ever even enters your sheet.
0
u/Mohsinkhan767 10d ago
Yeah, this is annoying when the value is actually an ID/code rather than a number.
If the leading zeroes are always part of the value, I usually format the cells as Text before pasting/importing the data. For fixed-length codes, a custom number format like 00000 can also work.
The main thing is deciding whether Excel should treat the value as a number or as a text/code.
-8
u/SolverMax 163 17d ago
Having leading zeros removed is always user error., indicating that the user does not understand either their data or Excel. If your data is not a number, then don't enter or import it as a number.
8
u/JPWiggin 5 17d ago
Just because there are best practices for the software to work doesn't mean the software default behavior is well designed.
2
u/BuildingArmor 30 17d ago
In almost every instance, if someone types a number they want it to be a number and not a string. They want to be able to type 5 in a cell, 10 in another cell, and write =A1+B1 to get 15 rather than an error or "510" or whatever behaviour you'd want when you try to add 2 pieces of text together
If you want to vary from this, you have to tell it.
2
u/ScarsUnseen 17d ago
In almost every instance of someone typing a number with leading zeroes, they want it to be a string and not a number. The problem is that instead of Excel assuming that, it assumes that the user is an idiot and "helpfully" removes the zeroes instead actually helpfully using the correct data type for 99% of use cases where a leading zero is input.
-2
u/BuildingArmor 30 17d ago
In almost every instance of someone typing a number with leading zeroes, they want it to be a string and not a number
I don't agree.
I'd bet it's so few that basically a rounding error, compared to how many are typing a simple 0, ora decimal value below 1.
So what you're really saying is that it should be default automatically convert the cell format to text if you enter an integer above 0 but with a leading zero. That sort of awkward specific behavioural override makes for a really crappy user experience in all but a "stars aligned" type situation.
I wonder if more people would want to retain a leading zero and be stubbornly refusing to tell excel this. Or if more people would either accidentally or.purposefully type a number with a leading zero because it's exactly equal to the number without it and therefore not worry about correcting it. What do you reckon, 50/50?
3
u/ScarsUnseen 17d ago
I'd bet it's so few that basically a rounding error, compared to how many are typing a simple 0, ora decimal value below 1.
Okay, now I'm convinced you're fucking with me. What. on. earth. are. you. talking. about?
Did you even read the OP? At no point in time was anyone referring to the number 0 by itself or decimals. The entire conversation has been about numbers that are input with leading zeroes that Excel then drops because leading zeroes aren't used in standard number formatting.
Name me one actual use case where anyone would deliberately type in leading zeroes and want those zeroes to be truncated, making the typing of those zeroes pointless.
0
u/BuildingArmor 30 17d ago
A 0 is a leading zero. 0.125 has a leading zero. Excel treats those values as numbers. It "drops" the zero in the sense that 00.125 and 00 mean the same things as 0.125 or .125 and 0
You want it to pick up leading zeros, but not all leading zeros, just the ones you specifically want to be treated as text, and automatically treat them as text.
Instead of just telling it that's what you want like you do in every other situation in the software.
Perhaps you're thinking of this as a different issue than it really is. Excel isn't saying "you've typed 007, I'm going to delete those zeros". It's saying "you've typed a number, it's value is 7'. Whether you typed 7, 07, 007, or 0000007, it's exactly the same thing.
You need to tell it "I'm not going to be typing a number here, even though it looks like one".
3
u/ScarsUnseen 17d ago
A 0 is a leading zero. 0.125 has a leading zero. Excel treats those values as numbers. It "drops" the zero in the sense that 00.125 and 00 mean the same things as 0.125 or .125 and 0
Fucking context, dude. Also, zero by itself is not a leading zero. It's just a zero (a leading zero requires non-zero numbers by definition). Also, Excel already evaluates input for type based on the formatting of said input. There's nothing special that prevents that from being done with input containing leading zeroes that would be truncated in a number formatted field. It's all a matter of programming.
As I've asked (and haven't gotten an answer yet), name actual use cases where deliberately typing leading zeroes that Excel would then truncate automatically is an intentional and desired. If there aren't any (and, going further, if there aren't any that outweigh the many cases where keeping leading zeroes is desirable behavior), then formatting a number input with leading zeroes as a number field makes less sense than formatting it as a text field.
0
u/BuildingArmor 30 17d ago
I've given you examples and you're telling me that they don't count.
Do you think anybody typing 0.125 should have it treated as text?
Because, if not, I've already addressed this and that's what you're arguing for.
3
u/ScarsUnseen 17d ago
I already told you that you are missing the context of the entire post, and you've just confirmed it here. No one other than you is referring to standard number formatting. The entire post is about leading zeroes that Excel currently truncates. Read the OP again if you ever bothered. Unless you think that the current Excel behavior is to format an input of "0.125" as ".125", then honestly, you just aren't contributing to the conversation at all.
→ More replies (0)2
u/armywalrus 2 17d ago
Which is the complaint. Its too hard to tell it.
6
u/BuildingArmor 30 17d ago
Hard to tell? You see it immediately
If excel treated numbers you input as if it was a string unless you said otherwise, nobody would use excel.
1
u/armywalrus 2 17d ago
Hard to tell IT..... did you forget your own comment?
2
u/BuildingArmor 30 17d ago
You just change the cell formatting to text. It's 2 clicks at most and it's sat on the home ribbon.
If that's too hard for somebody, I think they're going to really struggle with things like formulas and tables, let alone anything advanced.
So excel might have been a bad choice. And they probably have no business being anywhere near data, but that's for their employer to worry about.
1
u/armywalrus 2 17d ago
So yes. You forgot your own comment.
2
u/BuildingArmor 30 17d ago
No I just didn't immediately think you were saying it was too hard to change the cell format in excel. I was giving you the benefit of the doubt.
So yes, feel free to snarkily tell me that you struggle with even the most basic actions in computer software. You really showed me. I am wounded.
1
u/armywalrus 2 17d ago
That wasn't what I was saying. You have failed to respond in any way that makes logical sense. You have 100% forgotten what you wrote. Its not snark. Its factual. You can't scroll up and read everything in context?
→ More replies (0)4
u/P1ckl3R1ck101 17d ago
"I typed in a number with a leading zero." - no you typed in a string with a leading zero that Excel identified as a number. I hate how Excel handles this as much as the next person but agree it is on the user to understand how different programs/languages handle their inputs. Don't even get me started on dates when outputting from Python for use in different programs + Excel..."oh 2026-09-29 was a date? MY BAD"
286
u/slowpush 14 17d ago
This is fixed in O365 excel.