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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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
8,053
Office Version
  1. 365
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,669
Messages
5,838,684
Members
430,563
Latest member
Raeyven

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