Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate Problem | New Users to Excel | |||
CONCATENATE problem | Excel Worksheet Functions | |||
Concatenate Problem | New Users to Excel | |||
Concatenate Problem | Excel Programming | |||
concatenate problem | Excel Worksheet Functions |