Darvaish asks how to keep leading zeroes during data entry. If you type 0523 in Excel, it changes to 523. Today's episode offers three solutions.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast Episode 1878: Keep Leading Zero During Data Entry.
Hey, welcome back to MrExcel netcast, I’m Bill Jelen.
Today’s question is sent in by Darvish.
Darvish caught me on Facebook and said that sometimes when you’re entering data 1234, that works fine but if you do 0523, you’ll lose a leading 0.
What are some of the ways to keep the leading 0?
I have three different ways to solve this.
The first way, the way that I use most often is you’re coming along and you’re typing data and then all of a sudden, you realize that you have one that has a leading 0 or two leading 0s, just type a little ‘ there and then type the leading 0s and they will stay on.
Now, you see it’s left justified instead of right justified but we can fix that easy enough with the right justified.
All right, so that’s one method of doing it.
The other way is to select the area where you’re going to be entering the data and coming around the Home tab, change from General to a Text style and then when you are typing, so you use 1234, 2345, 0523, 0009 and the leading 0s will stay there.
The problem with both of these methods though is that when we use the SUM function to add these up so if I do =SUM(C4:C7) here, the text values are not going to be added in.
And so, you see that 7903, down here at the bottom right-hand corner is the total of those selected cells and that is the total that they’re giving us so the 0052 is not there.
I’m not ever recommending this but if you would directly refer to that cell, then it will continue to work but this is a really bad way to build that formula.
I would never, ever suggest that.
So, I think the best way to do this overall is to select the range we’re going to be entering the data.
We’ll press Ctrl+1 to get to Format Cells.
On the Number tab, go to custom and we’re going to create a custom number format with four 0s. 0000, which say, “Hey, always display four digits” even if I only enter two digits 123, we get a leading 0, 234, 23, 59 and we always have the 4 digits.
What if I would type more than that?
12345.
Sure, it shows, all right.
And the beautiful thing about this is when we add those numbers up, they are really numbers and it will make sure that it is the right total 14018.
So, the Custom Number Format is the one I would go with especially if we can predict that it’s always going to be four digits then that is it.
I want to thank Darvish for sending that question in and I want to thank you for stopping by.
We’ll see you next time for another netcast with MrExcel.
Learn Excel from MrExcel Podcast Episode 1878: Keep Leading Zero During Data Entry.
Hey, welcome back to MrExcel netcast, I’m Bill Jelen.
Today’s question is sent in by Darvish.
Darvish caught me on Facebook and said that sometimes when you’re entering data 1234, that works fine but if you do 0523, you’ll lose a leading 0.
What are some of the ways to keep the leading 0?
I have three different ways to solve this.
The first way, the way that I use most often is you’re coming along and you’re typing data and then all of a sudden, you realize that you have one that has a leading 0 or two leading 0s, just type a little ‘ there and then type the leading 0s and they will stay on.
Now, you see it’s left justified instead of right justified but we can fix that easy enough with the right justified.
All right, so that’s one method of doing it.
The other way is to select the area where you’re going to be entering the data and coming around the Home tab, change from General to a Text style and then when you are typing, so you use 1234, 2345, 0523, 0009 and the leading 0s will stay there.
The problem with both of these methods though is that when we use the SUM function to add these up so if I do =SUM(C4:C7) here, the text values are not going to be added in.
And so, you see that 7903, down here at the bottom right-hand corner is the total of those selected cells and that is the total that they’re giving us so the 0052 is not there.
I’m not ever recommending this but if you would directly refer to that cell, then it will continue to work but this is a really bad way to build that formula.
I would never, ever suggest that.
So, I think the best way to do this overall is to select the range we’re going to be entering the data.
We’ll press Ctrl+1 to get to Format Cells.
On the Number tab, go to custom and we’re going to create a custom number format with four 0s. 0000, which say, “Hey, always display four digits” even if I only enter two digits 123, we get a leading 0, 234, 23, 59 and we always have the 4 digits.
What if I would type more than that?
12345.
Sure, it shows, all right.
And the beautiful thing about this is when we add those numbers up, they are really numbers and it will make sure that it is the right total 14018.
So, the Custom Number Format is the one I would go with especially if we can predict that it’s always going to be four digits then that is it.
I want to thank Darvish for sending that question in and I want to thank you for stopping by.
We’ll see you next time for another netcast with MrExcel.
