concatenate problem
 

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

concatenate problem
 

1) =TEXT(A1,"0000000000")
2) =LEFT("0000000000",10-LEN(A1))&A1
3) why not give A1 a custom format of "0000000000" ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lindy" wrote in message
...
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.
--
Lindy

concatenate problem
 

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



--
Gary''s Student - gsnu200810


"Lindy" wrote:

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

concatenate problem
 

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

=Text(A1,"0000000000")

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Lindy" wrote:

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

concatenate problem
 

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

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

--

Regards,
Nigel




"Lindy" wrote in message
...
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.
--
Lindy

concatenate problem
 

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


"Nigel" wrote:

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

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

--

Regards,
Nigel




"Lindy" wrote in message
...
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.
--
Lindy

concatenate problem
 

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


"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200810


"Lindy" wrote:

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

concatenate problem
 

Then copy the formula down the column by double clicking the fill handle
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lindy" wrote in message
...
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


"Nigel" wrote:

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

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

--

Regards,
Nigel




"Lindy" wrote in message
...
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.
--
Lindy

concatenate problem
 

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

"Lindy" wrote in message
...
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


"Nigel" wrote:

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

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

--

Regards,
Nigel




"Lindy" wrote in message
...
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.
--
Lindy

concatenate problem
 

See my first message- pint 3
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

"Lindy" wrote in message
...
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


"Nigel" wrote:

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

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

--

Regards,
Nigel




"Lindy" wrote in message
...
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.
--
Lindy