Excel leading zeros help | Off Topic | unofficial empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

You are not logged in. [Log In] empegbbs.com » Forums » General Discussion » Off Topic » Excel leading zeros help

Topic Options

#355957 - 30/10/2012 11:51 Excel leading zeros help
tahir pooh-bah Registered: 27/02/2004 Posts: 1919 Loc: London	I need to join two columns in Excel: Column A: "A032" Column B: "00001" (Custom format 00000) My join is A1 & "_ " & B1 which should result in "A032_00001" but I'm ending up with "A032_1". How can I preserve the leading zeros?
Top

#355958 - 30/10/2012 12:01 Re: Excel leading zeros help [Re: tahir]
Tim veteran Registered: 25/04/2000 Posts: 1529 Loc: Arizona	Instead of using a custom format, can you just use a ' as the first character in the 00003 cell? That works for me when I tested it out. Edit: Such as '0001 or since my memory is apparently shot, '00003 in my statement.
Top

#355961 - 30/10/2012 12:24 Re: Excel leading zeros help [Re: Tim]
tahir pooh-bah Registered: 27/02/2004 Posts: 1919 Loc: London	OK problem is that I can't use a ', it's a (calculated) sequential number
Top

#355963 - 30/10/2012 12:59 Re: Excel leading zeros help [Re: tahir]
andy carpal tunnel Registered: 10/06/1999 Posts: 5916 Loc: Wivenhoe, Essex, UK	I don't think you can preserve the leading zero, as when it comes down to it tje value of that zero is 1 and not 00001. However you can do the formatting in the formula, which will achieve much the same thing (if not so convenient): =A1&TEXT(B1,"00000") _________________________ Remind me to change my signature to something more interesting someday
Top

#355965 - 30/10/2012 13:04 Re: Excel leading zeros help [Re: andy]
tahir pooh-bah Registered: 27/02/2004 Posts: 1919 Loc: London	Brill, thanks Andy
Top

#355966 - 30/10/2012 13:05 Re: Excel leading zeros help [Re: tahir]
Tim veteran Registered: 25/04/2000 Posts: 1529 Loc: Arizona	I was going to suggest something much uglier... = A1 & "_" & IF(B1<1000,IF(B1<100,IF(B1<10,"000" & B1, "00" & B1),"0" & B1),B1)
Top

#355971 - 30/10/2012 17:03 Re: Excel leading zeros help [Re: Tim]
tanstaafl. carpal tunnel Registered: 08/07/1999 Posts: 5549 Loc: Ajijic, Mexico	Originally Posted By: Tim I was going to suggest something much uglier... Uglier, maybe, but much more fun! tanstaafl. _________________________ "There Ain't No Such Thing As A Free Lunch"
Top

#355996 - 31/10/2012 08:55 Re: Excel leading zeros help [Re: tanstaafl.]
tahir pooh-bah Registered: 27/02/2004 Posts: 1919 Loc: London	Fun? Fun in Excel?????
Top

#355997 - 31/10/2012 09:38 Re: Excel leading zeros help [Re: tahir]
Tim veteran Registered: 25/04/2000 Posts: 1529 Loc: Arizona	Originally Posted By: tahir Fun? Fun in Excel????? I had to write a mission simulator for a customer using Excel. That was kind of fun.
Top

#355999 - 31/10/2012 10:05 Re: Excel leading zeros help [Re: Tim]
tahir pooh-bah Registered: 27/02/2004 Posts: 1919 Loc: London	Sounds scary to me
Top

#356019 - 31/10/2012 14:52 Re: Excel leading zeros help [Re: Tim]
canuckInOR carpal tunnel Registered: 13/02/2002 Posts: 3212 Loc: Portland, OR	Originally Posted By: Tim Originally Posted By: tahir Fun? Fun in Excel????? I had to write a mission simulator for a customer using Excel. That was kind of fun. The first word my eyes picked out of this sentence was mission, and, without seeing who wrote it, yet, assumed it was Doug chiming in with something about "fun in Excel" being his personal mission statement.
Top

View All Topics