How do I convert 2 digit numbers in a column to 4 digits?

[versuskasparov]

Hi,
</SPAN>
With Excel 2007 (Windows 7) I wanted to convert 2 digit numbers (e.g. 12, 23, 55) to 4 digits (e.g 0012, 0023, 0055). I selected the 2 digit numbers in a column, then clicked on ‘Format’ in the Cells group on the Home tab and then clicked on ‘Format Cells.’ This brought up the Format Cells dialog box and I clicked on the Number tab. In the category list, I clicked on custom. I then chose ‘0’ (zero) and modified it by adding three more zeros to make it for digits. I then clicked OK and my 2 digit numbers became 4 digits with two leading zeros. The problem I have is that even though four digits numbers now appear in the cell, when I click on any cell that contains these formated numbers I see only two digit numbers in the Formula Bar. I tried solving the problem by copying them and pasting them as values using “paste special” to no avail. The worst part is that when I concatenate (join) the column containing these numbers with another column, the numbers default back to being 2 digits. Pls how do I change 2 digit numbers to 4 digits permanently?

Much Obliged!

 

[PaddyD]

Hi - welcome to the board.

=text(a1,"0000")

...but be a bit careful - the values will now be text, not numbers, so a range of mathematical functions won't work on them after the transformation.

 

[GTO]

...The problem I have is that even though four digits numbers now appear in the cell, when I click on any cell that contains these formated numbers I see only two digit numbers in the Formula Bar

That is as it should be. You can change the cell's formatting as you have done, for display and printing purpose, but the formula bar will display the actual values; so leading zeros will get dropped.

...The worst part is that when I concatenate (join) the column containing these numbers with another column, the numbers default back to being 2 digits. Pls how do I change 2 digit numbers to 4 digits permanently?...

If you are actually concatenating, like 001 & 002 would return 001002, then the act of concatenation results in 001002 being read as a string. Is that what you want?

 

[Scott Huish]

If you are actually concatenating, like 001 & 002 would return 001002, then the act of concatenation results in 001002 being read as a string. Is that what you want?

That's not true, if the cells values are just formatted to have the leading zeros.

=TEXT(A1,"0000")&TEXT(B1,"0000")

 

[versuskasparov]

That's not true, if the cells values are just formatted to have the leading zeros.

=TEXT(A1,"0000")&TEXT(B1,"0000")

Sorry for the delay in reply. I went to bed right after posting the question and just woke - I guess we live in different time zones - I used your formula and thereafter </SPAN>pasted as values using paste specail and YES IT WORKED!!! Thanks a million. You are a lifesaver!