Results 1 to 5 of 5
Thread: Adding leading zero's (2003)

20060601, 04:15 #1
 Join Date
 Mar 2003
 Posts
 191
 Thanks
 0
 Thanked 0 Times in 0 Posts
Adding leading zero's (2003)
Hello all,
I have inherited a large spreadsheet which I have to do some repairs on. One of the problems I have is a column that contains only numbers, but is formatted as Text. I need for this field to display every number as five digits. The problem is that previously this column was formatted as General and all of the leading zeros have dropped off, leaving only 1, 2 3, or 4 digits in the column in a lot of cases.
For example 123 should be displayed as 00123, or 1 should be displayed as 00001.
This workbook contains 10000 records so changing them individually is out of the question. Is there a way I can change all numbers of less than 5 digits to display leading zeros to make up my five digit number?
By the way I have now formatted that column as Text if that helps.
Thanks heaps.
Bill

20060601, 04:26 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Adding leading zero's (2003)
1) Select a blank cell (formatted as General)
2) Copy it to the clipboard (Edit  Copy)
3) Select the cells that need 5 digits.
4) Select Edit  Paste Special...
5) Click on Add, then OK.
6) Select Format  Cells...
7) Activate the Number tab.
8) Select the Custom category.
9) Enter 00000 in the Type box.
10) Click OK.

20060601, 04:28 #3
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Adding leading zero's (2003)
You could use Custom Number Formatting!
Select the column and press CTRL + 1 (to open the Format Cells Dialog)
Select the numbers tab and choose Custom at the bottom of the list
In the General text box, type  00000
Chose OK
This will format the numbers to 5 digits with leading zeros where neededRegards,
Rudi

20060601, 04:38 #4
 Join Date
 Mar 2003
 Posts
 191
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding leading zero's (2003)
Rudi/Hans,
Thank you both for your help.
Both solutions worked perfectly. Much appreciated.
Bill

20060601, 14:18 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding leading zero's (2003)
Hans and Rudi have told you how to display the number values with leading zeros. However, your post indicated that you really wanted to change the numeric values to text values with leading zeros. Just changing the format to text will not convert a numeric value that is already in the cell to a text value. If you really want to change the values to text (and I think that is what should be done if these are not values that will be used in calculations), then the procedure below will do that. The procedure below assumes that the values are in column A, if that is not the case, modify it as required.
1 Select an empty column and enter this formula in cell 1 of that column:
<code>
=TEXT(A1,"00000")
</code>
2 Fill that formula down the column as far as the numbers go in column A. You should now have text with leading zeros displayed.
3 Select column A and make sure the format of all the cells is TEXT.
4 Select the column with the formula above and do an Edit/Copy.
5 Select cell A1 and then do Edit/Paste Special.
6 In the Paste section of the Paste Special dialog box, put a tick mark next to Values. Click on OK.
7 You can now delete the column with the formulas and you should have text values in column A. Any new values you enter in column A should be entered as text values.Legare Coleman