concatenate problem

[Lindy]

Help Urgent

I have a column with variable length numbers. I need to put these in a new
column with leading zeros in front to make the length of the new cell to 10.
Example for cell with 23, I need a new column to hold 0000000023.

 

[Bernard Liengme]

1) =TEXT(A1,"0000000000")
2) =LEFT("0000000000",10-LEN(A1))&A1
3) why not give A1 a custom format of "0000000000" ?
best wishes

 

[Gary''s Student]

For column A into column B:

Sub LeadingZero()
Dim s As String
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
v = Cells(i, "A").Value
s = Application.WorksheetFunction.Rept("0", 10 - Len(v))
Cells(i, "B").NumberFormat = "@"
Cells(i, "B").Value = s & v
Next
End Sub

 

[Alan Moseley]

Assuming that your 23 is in A1, use the formula:-

=Text(A1,"0000000000")

 

[Nigel]

With number in cell A1 use this in another cell to create the string you
require....

=CONCATENATE(REPT(0,10-LEN(TRIM(A1))),A1)

 

[Lindy]

The column has about 3000 rows so I need a way to do this for every row in
the column considering variable length of the contents of the cells.

 

[Lindy]

Thank you. This works like a charm. Just takes a bit of time due to the
lenght of the column

 

[Bernard Liengme]

Then copy the formula down the column by double clicking the fill handle

 

[redbird]

You could simply format the column as "0000000000" if it is for display only
(as long as the column contains only numbers)

 

[Bernard Liengme]

See my first message- pint 3
best wishes