Need a solution to edit numbers

Here's my problem: Several months ago I had edited an excel price list file for importing into a inventory management program. This price list has a lot of numbers that begin with a 0 (zero), and Excel gives the error "Number stored as text". Well, I didn't know at the time, and I'm very inexperienced with Excel, but I must have converted those numbers stored as text to numbers, and it eliminated all the leading zeros. Now, it came time to update the price list, and I have all these incomplete item numbers, so I can't update the program properly. I'm hoping to find a way to add zeros in front of these incomplete numbers, in bulk (there's several thousand rows of them). But I don't know how. The problem is that the incomplete numbers are mixed right in with the correct numbers. I know how to add something to all the numbers, but I can't figure out how to isolate the bad numbers. There'd have to be a way, because they are unique. The item numbers are supposed to be 6 digits (there are a few 8 digit numbers also). The numbers that had their zeros cut off are 5 digits or less (Just a handful had more than one leading zero). So what I'm wondering is if there's any way to isolate all the five digit numbers. Then I could easily do what I want with them. I've tried sorting the item number column, but it doesn't group the five digit numbers. Here's an example of the numbers:

Correct item number (btw, all the numbers have the same two character letter prefix; the zeroes were deleted before I added the prefix):

AB123456

Incorrect:

AB12345 (should have been AB012345)


This is putting me in a real pinch and I'm hoping there's a way to do it other than scrolling through the whole thing and manually adding zeros before the item numbers that only have 5 digit numbers

Try:

=LEFT(A1,2)&TEXT(RIGHT(A1,LEN(A1)-2),"000000")

where A1 houses original string


You can then copy and paste special >> Values over the original data

Try:

=LEFT(A1,2)&TEXT(RIGHT(A1,LEN(A1)-2),"000000")

where A1 houses original string


You can then copy and paste special >> Values over the original data

yes, that's right.... you would put that formula in an empty column starting at row 2..then copy it down.

Once you've got your results copy the new column and then select B2 and go to Edit|Paste Special and select Values... this should copy the new results over the old.

Now you can delete the column with the formulas.