Adding a leading zero to numbers

[siasarma]

I have a list of a couple thousand customer numbers. Some of them are 5 digits long and some are 6. I was just told that we have to have them all the same number of digits long. So, I am going to add a leading zero to them. Example the number is 12345 I want to make it 012345. Does anyone know of an easy way to format all these to have a leading zero, so I don't have to go through a couple of thousand of these and type a zero in there??? Thanks!

 

[MrKowz]

Edit:

Try =IF(LEN(A1)=5,TEXT("0"&A1,"000000"),A1)

 

[barry houdini]

You could use a custom format

000000

 

[Mike Blackman]

Hi,

For this you would need to format the cells as text;

Right Click - Format - Number Format - Text

And then you will be able to add the first zero's back in.

You could use this code to put the zero's in for you;

Dim lRow As Long

lRow = Range("A" & Rows.count).End(xlUp).Row

For i = 2 To lRow
    If Len(Cells(i, 1)) = 5 Then
        Cells(i, 1) = "0" & Cells(i, 1).Value
    End If
Next i

 

[siasarma]

Thanks! That was easy!!!