leading zero problem

[rjc008]

I've searched the forum, because I'm sure that this is a frequent problem. But it seems that all the answers are specific to their problems.

Anyways, I have column with most as 5 digits, but the one's that started with a "0" were left off. I don't have the original file anymore to copy and paste again, but I have since changed the column to text.

Can someone help me, to construct a formula if there are only 4 charactes then add a zero in front.

Thanks.

[Richard Schollar]

=RIGHT("0"&A1,5)

Richard

[JBeaucaire]

Just a thought, if there is a chance ANY of those numbers are less than 4, this would insure you always get 5 characters, no matter what:

Please Login or Register  to view this content.

[davesexcel]

Try A custom format
Select a range to format, right click with mouse and select format cells=>number=>custom in the type box enter ....
00000
Then OK
So now when ever you have less than 5 digits, the number will be lead with zeros.
It will also work if you select special=>zip code in the same tab

[Shijesh Kumar]

Select The column

Right Click and Select Format Cell

Select Custom and in the Type: Box enter - '@'

[rjc008]

Great, the format cells, custom thing worked perfectly. I saw that solution in an old thread, but I didn't do it properly, your explanation was better.

Thanks.