Adding the digit "0" to a number

[stigvage]

Hi

I'm trying to code a loop which runs through every cell in a specified column.

If the cell contains a number with 10 digits, then add "0" to the left side of the number.

For example 1126742873 = 01126742873

It's an emplyee-number and the zero has to be there, with usual formatting it gets wrong.

Anyone who can help with this?

Best regards
Stig

 

[QuietRiot]

Dim j As Range
For Each j In Range("Y1:Y" & Range("Y" & Rows.Count).End(xlUp).Row)
If IsNumeric(j) Then
    If Len(j) = 10 Then
        j.NumberFormat = "@"
        j = 0 & j.Text
        End If
    End If
Next

 

[Oaktree]

If it's just for display purposes, you can just format the cells as custom 00000000000 (eleven zeroes).

Or, in an unused column, Z1=TEXT(A1,REPT("0",11))
Then, copy Z1 down through the end of your range and copy --> paste special --> values from column Z to column A.

 

[stigvage]

Thanks.

Works perfect

Stig