concatenate problem

L

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.
 
B

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
 
G

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
 
N

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)
 
L

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.
 
L

Lindy

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

redbird

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