MrExcel Consulting
Your One Stop for Excel Tips & Solutions

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.

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

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

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