Archive of Mr Excel Message Board


Back to General Excel archive index
Back to archive home

Padding out a cell with leading zeros

Posted by Adrian King-Cox on April 18, 2001 2:45 AM
I've got a 30,000 record database with a numeric field which needs to be padded-out with leading zeros, making the field 8 chars, i.e. 1 becomes 00000001, 11 becomes 00000011 and so on. I've tried a few complex formulas, converting to text and so on but no joy. Does anyone have any bright ideas? The completed file needs to be exported as a CSV.

Re: Padding out a cell with leading zeros

Posted by Dave Hawley on April 18, 2001 3:01 AM


Hi Adrian

This will do the trick:
=REPT(0,8-LEN(A1))&A1


Dave

OzGrid Business Applications


Re: Padding out a cell with leading zeros

Posted by Richie Turner on April 18, 2001 5:19 AM

Hi Adrian

Can you not just give the numeric cells a custom format of 00000000, this will pad out all numbers to 8 digits, 12 becoming 00000012 etc.
I've tried this and saved as CSV, opened in notepad and it's fine.

Hope this is a simple answer to your problem

Richie


Re: Padding out a cell with leading zeros

Posted by David Hawley on April 18, 2001 6:13 AM


Richie, I think your onto something here! I like that.

Dave

OzGrid Business Applications


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.