Excel Question

Excel Question

I've run into an annoying problem. I have a large spreadsheet with about 14,000 records. One column consists of zipcodes...some of which were entered without leading zeroes (those New England zipcodes such as 01238). I can force them to display with leading zeroes by using the Format Cells command, but in this case, I need to combine the zipcode with another field to use for key lookup. Because those leading zeroes aren't actually in the field, they don't get used.

Well, the obvious fix is to use the keyboard macro feature to go through and enter a leading zero in each of these hundreds of fields...but that doesn't work. It turns out that whatever the value of the field is when you record the macro is used for all subsequent fields. So if you have:

1255
1258
1231

You record the macro with F2 (to go into the Formula bar), use the Home button to go to the start of the field, then enter a 0, down arrow, and enter.

But the next time you run the macro, in the cell with 1258, even though you didn't actually type 1255 when you recorded the macro--that's what it drops into the cell. Any suggestions?

I wish Excel had a regular expression Find and Replace. It's weird, because Word does, and has had it for years.

UPDATE: That was quick! A reader suggested:

In a new blank column, enter the following formula:

=IF(LEN(A1)<5,rept("0",5-len(a1))

then drag it down to the rest of the column. You should get back all of the zipcodes padded to 5 characters, and then you can either use those values, or copy/Paste Special (values) over the original column.

It worked perfectly, my problem is solved!