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

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.

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 needed

Rudi/Hans,
Thank you both for your help.
Both solutions worked perfectly. Much appreciated.

Bill

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•