PDA

View Full Version : Solved: add leading zeros with a specified length of characters



libertarian
04-05-2009, 03:13 PM
I am trying to create a file of geocodes that can be imported into ArcGIS. Therefore I need, in this case, the 3-digit county geocode to contain exactly three digits. For example, county number 7 needs to read as "007" and county number 67 needs to read as "067". I need to format the entire county column in this manner. I've been trying variations of the =REPT("0",3-LEN(B2))&B2 but this populates EVERY cell in the column with whatever happens to be in B2. Can I use a variation of this formula to reference whatever row in Column B is the row of the cell I want changed? Otherwise I have to re-enter a modified REPT function whenever the county code changes.

Bob Phillips
04-05-2009, 04:09 PM
I am not clear, can you give an example of what you mean?

tpoynton
04-05-2009, 05:29 PM
this seems to work, and you can use autofill to put the formula in another column, if I understand correctly.

=IF(LEN(B2) < 3, REPT("0", (3-LEN(B2)) )& B2, B2)

lucas
04-05-2009, 05:37 PM
A vba possible solution:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=946

libertarian
04-05-2009, 08:08 PM
Okay, suppose I have a column with 50,000 rows. The cell values may contain the following: 7, 9, 11, 67, 101, 114. What I would want them all to say would be 007, 009, 011, 067, 101, 114. Therefore I want three characters, whatever the original value in the cells.

mdmackillop
04-06-2009, 04:12 AM
Format Cells/Number/Custom/Type 000

tpoynton
04-06-2009, 09:35 AM
Format Cells/Number/Custom/Type 000

Much easier!

Bob Phillips
04-06-2009, 10:15 AM
Much easier!

Aah, yes ... but the OP said ... I am trying to create a file of geocodes that can be imported into ArcGIS.

lucas
04-06-2009, 06:09 PM
If I understand Bob's meaning then the zero's have to actually be there...this may be a little slow on 50,000 rows.

Bob Phillips
04-07-2009, 01:30 AM
If I understand Bob's meaning then the zero's have to actually be there...

That is certainly how I read it Steve, to be there in the data rather than just the presentation of data for exporting.