Excel 2007 : "text" with numbers

[MARKFDU]

I am trying to display/store a zip code including a leading zero. I change the format options to "TEXT", set up each find/replace as "TEXT", but the zip stubbornly refuses to add that zero. What I am doing wrong?

[jeffreybrown]

Excel is seeing the zip as a number so it drops the proceeding zero...

Enter the zip as >> '01212

[MARKFDU]

This changes only the target zip. How do I change the 300+ in the column?

[jeffreybrown]

Select the column of 300+, go to the VBE (Alt + F11), insert new module, paste this macro on the right side of the screen and run.

Please Login or Register  to view this content.

[MARKFDU]

What if zip is "11111" ? Then the zero is NOT wanted.

[jeffreybrown]

This macro does not add a zero. Did you run it?

[MARKFDU]

So, the apostrophe means "keep what ever there is, AS IS", correct? Why in heck doesn't "TEXT" do the same ???

[jeffreybrown]

Not really sure why, but the apostrophe means to look at the entered value as text even though it is a number.

Also, usually when something is left aligned it is text, but when right aligned, a number (of course this is before somebody manipulated the cell).

Try this, type 1111 in a cell. After you hit enter it should by default right align since it is a number. Now go back into the cell with the 1111 and before 1111 enter the apostrophe. Now after you hit enter Excel treats it as text so it should left align

[MARKFDU]

Our zip code data field is LEFT-aligned in our file, as it ought to be. So why isn't it treated as text? 01234 should remain 01234 when excel-formatted as "text"!

[jeffreybrown]

As far as I understand, it is treated as text until you touch that cell. I have data in which I also extract from another system with value such as 0004, 0104, 0160, etc.

As soon as I enter one of these cells and then exit, Excel now decides that cells is a number so it drops the leading zero. I end up with 4, 104, 160.

Not sure I can give you a better explanation than this. Maybe one of the more experienced Excellers can give a better answer.

[MARKFDU]

OK. That being the case, why doesn't excel allow a no-questions-asked REformat, back to text, and then treat it as such? Seems a major flaw in excel.

[jeffreybrown]

Could be, but at this time I don't have a dog in that hunt

[MARKFDU]

Thanks for helping out, Jeff

[jeffreybrown]

You're welcome