Adding the digit "0" to a number

stigvage

New Member
Joined
Aug 13, 2007
Messages
9
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
Code:
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,549
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top