Leading zeros in Excel
This is a fun solution. You want to put a series of numbers in a range in Excel and you want them to have leading zeros. So you want, 001, 002, 003 etc. Problem is that Excel drops the zeros when you type them. It makes sense, leading zeros aren't required and really don't aid comprehension. However, for your own reasons, you want them.
Here's how to do this:
Select the cells and choose Format > Cells > Number tab. Select Custom from the Categories list and in the box marked Type:, type this:
00#
This tells Excel that there must always be 3 digits showing which forces leading zeros to be displayed. It doesn't do anything to the numbers so they are still numbers which is just as you would want it to be.
Labels: Excel, leading zeros
posted by Jane @ 10:43 AM
9 Comments:
This was an incredibly AWESOME tip!!
I work with upc codes, and several thousand of them have a leading zero. Every time I import those, it would cause a problem in the table.
My developers were clueless---could not resolve it other than change the column to text after the csv was created, and then manually add the zero to the front of those that had been cut off.
Thank you so much!
Great tip, THANK YOU! I had been coverting to text, & concatenating the right number of zeros in lists of SSNs. This is so much easier.
EXCELLENT Answer! The other methods we were experimenting with were vastly more complex and didn't work as well. This solution is smart enough to change the number of leading zeros needed to maintain the desired number of digits, even not adding them at all if a number is correct to start with. Hat's off to you!!
However, if you concatenate numbers formatted this way, the leading zeros disappear. Is there any way to apply text formatting to an existing column of numbers without retyping all of them?
The problem that you’re about to face in the solution you propose is, yet again, disappearing zeros. When you type a number in an Excel cell any leading zeros are removed when you press Enter. So, for example, if you type 00310 the actual cell contains 310. Even when you apply a custom format like 00000 to the cells you get the zeros showing but they aren't actually added to the underlying cell entry. So, simply changing the formatting to text format won't work. However, you can solve the problem with a macro. In Excel VBA there are lots of different cell properties you can access – the value property lets you access the contents of the cell such as its formula or the number that is stored in it. The text property gives you the information that is displayed in the cell on the screen. For your solution, you'll use both properties in a macro to first extract the text property of a cell, then change the cell's format and finally, feed the saved text property back in as the new value property.
This macro does the work:
Option Explicit
Public userRange As Range
Sub changeCells()
Dim cellContents As String
Dim cell As Range
'get range from user
Set userRange = Application.InputBox(prompt:="Select a range to change to text", Default:=ActiveCell.Address, Type:=8)
'process each cell in range
For Each cell In userRange
'store displayed value
cellContents = cell.Text
'change cell format to text
cell.NumberFormat = "@"
'set value property to the stored display value
cell.Value = cellContents
Next cell
End Sub
To create the macro, choose Tools > Macro > Macro, type the macro name, changeCells, determine whether you want to store the macro in the current workbook or personal.xls and choose Create. Type the macro code between the Sub and End Sub statements. When you run the macro you will be prompted to select the range of cells to alter. The macro will then process each cell saving you having to retype all the cell values. Once you’ve done this you can then concatenate your cell values without losing your zeros.
You are brilliant! Thanks. I have read a lot of postings and this is the first one that solves my recurring problem. Thanks again.
I am so grateful for this post. How great is it that I can type such an odd request into Google and find this solution immediately! Thank you, this was perfect.
You are very good, but does anyone have a clue why this and other obvious formats are not a part of Excel? Seems I am always trying to figure out something that seems common, but needs a VB macro or you need to stand on your head and scream like a chicken to get it to work.
Hi Helen. I am often awestruck by your skills and knowledge across a wide range of subjects. Have you ever considered publishing a special issue of PC User with all your historical tips and spreadsheets in it?
However, though the drive roared and rattled, I could not find any tutorial on the March 2010 DVD about Ecel String Functions. Ken Pettit
Post a Comment
Please feel free to add your comment here. Thank you!
Subscribe to Post Comments [Atom]
<< Home