PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > concatenate problem

Reply
Thread Tools Rate Thread

concatenate problem

 
 
Lindy
Guest
Posts: n/a
 
      29th Oct 2008
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 With Quote
 
 
 
 
Bernard Liengme
Guest
Posts: n/a
 
      29th Oct 2008
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" <(E-Mail Removed)> wrote in message
news:49CD2088-D93D-4DB3-971B-(E-Mail Removed)...
> 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 With Quote
 
Gary''s Student
Guest
Posts: n/a
 
      29th Oct 2008
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
 
Reply With Quote
 
Alan Moseley
Guest
Posts: n/a
 
      29th Oct 2008
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
 
Reply With Quote
 
Nigel
Guest
Posts: n/a
 
      29th Oct 2008
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
(E-Mail Removed)



"Lindy" <(E-Mail Removed)> wrote in message
news:49CD2088-D93D-4DB3-971B-(E-Mail Removed)...
> 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 With Quote
 
Lindy
Guest
Posts: n/a
 
      29th Oct 2008
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
> (E-Mail Removed)
>
>
>
> "Lindy" <(E-Mail Removed)> wrote in message
> news:49CD2088-D93D-4DB3-971B-(E-Mail Removed)...
> > 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 With Quote
 
Lindy
Guest
Posts: n/a
 
      29th Oct 2008
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
 
Reply With Quote
 
Bernard Liengme
Guest
Posts: n/a
 
      29th Oct 2008
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" <(E-Mail Removed)> wrote in message
news:51D8554C-9F7C-4F0C-8550-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>>
>>
>>
>> "Lindy" <(E-Mail Removed)> wrote in message
>> news:49CD2088-D93D-4DB3-971B-(E-Mail Removed)...
>> > 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 With Quote
 
redbird
Guest
Posts: n/a
 
      30th Oct 2008
You could simply format the column as "0000000000" if it is for display only
(as long as the column contains only numbers)

"Lindy" <(E-Mail Removed)> wrote in message
news:51D8554C-9F7C-4F0C-8550-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>>
>>
>>
>> "Lindy" <(E-Mail Removed)> wrote in message
>> news:49CD2088-D93D-4DB3-971B-(E-Mail Removed)...
>> > 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 With Quote
 
Bernard Liengme
Guest
Posts: n/a
 
      30th Oct 2008
See my first message- pint 3
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"redbird" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You could simply format the column as "0000000000" if it is for display
> only (as long as the column contains only numbers)
>
> "Lindy" <(E-Mail Removed)> wrote in message
> news:51D8554C-9F7C-4F0C-8550-(E-Mail Removed)...
>> 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
>>> (E-Mail Removed)
>>>
>>>
>>>
>>> "Lindy" <(E-Mail Removed)> wrote in message
>>> news:49CD2088-D93D-4DB3-971B-(E-Mail Removed)...
>>> > 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 With Quote
 
 
 
Reply

« Error on connection to recordset | Cell Validation Help »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate problem harwookf Microsoft Excel Misc 7 13th Jul 2009 04:46 PM
Odd Concatenate problem. Paul Microsoft Excel Worksheet Functions 9 13th Dec 2007 04:45 AM
Concatenate Problem singh Microsoft Excel Misc 3 9th Feb 2007 06:50 PM
RE: concatenate problem =?Utf-8?B?T2xh?= Microsoft Excel Worksheet Functions 0 28th May 2005 10:10 AM
Re: concatenate problem N Harkawat Microsoft Excel Worksheet Functions 0 27th May 2005 07:44 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 06:25 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top