Excel Leading Zeros, store them, not just format them
Posted on 2009-12-30
Several times each week, we get a file containing thousands of zip codes, stored as numbers (without leading zeros), but formatted with 00000 to get the leading zero's as appropriate.
If I click in the cell fore one of the zip codes, of course, there is no leading zero, just the number. I really want the cell to be text with the leading zero.
What's the quickest/simplest way to convert the entire column of numbers (formatted with leading zeros) into text, preserving the leading zero's without using apostrophy???
I know I could insert a temporary column, and use function: =TEXT(B3,"00000"), drag it down, insert another column, copy, paste special values, but there's going to be a lot of columns like this, and I get two files a week.
I'd like to avoid using macros or anything complicated because the person who will be doing it going forward is not very good in excel.
I've tried a variety of crazy things, even saving the .xls to CSV, and trying to import it back, but alas, it keeps wanting to store as number.
I was hoping there would be some wizard base method like text to columns but I can't seem to make it do what I want.
Any ideas would be greatly appreciated!