Error checking in Excel

Hi Jacsdad,

We're still working on this issue and we have did some editing to the sample workbook you provided. We used Text to Columns to convert the data in the problematic column and see that the error disappears.

The ZIP codes you entered in the column may have came from different sources and not typed by hand, so the data type might not have been stored as a Number.

We have tried to clean your workbook and we have not seen the error pop up again. We will provide it via private message, please use it for a few days and let us know if the error shows up again.

For your reference:

Convert numbers stored as text to numbers

Top ten ways to clean your data

Regards,

Sheen

Hi Jacsdad,

I apologize if my wording came off as harsh. I assure you that was not my intention. 

I understand from your point of perspective that a cell formatted as Special> ZIP code should not be considered as Text as well as Number, and is special, which would not show the error Excel is giving, even with the Error Checking on.

I will involve the related team for further investigation. We also suggest providing feedback from Excel so that the team can directly receive your feedback and take it into account.

Regards,

Sheen

It's become obvious that my point is not getting accross.

Within Excel preferences, there is ab option to format the data as a Zip Code (see attached).  With advice coming from so many directions and this last submitter being harsh, hinting I don't know what I am doing, it's best to just drop the subject.

Hi Jacsdad,

First, regarding the error, based on the article about postal codes, we feel like this is an expected behavior:

Display numbers as postal codes

If you refer to the second note, we can see that when saving them as a ZIP code, we do want it to be formatted as text instead of numbers:

If you're importing addresses from an external file, you may notice that the leading zeros in postal codes disappear. That's because Excel interprets the column of postal code values as numbers, when what you really need is for them to be stored and formatted as text. 

Right now, we would like to prevent Excel in displaying this error instead. Under Excel's Preferences > Error Checking, there should be a special option to ask Excel to not show errors for Numbers formatted as text.

We suggest unchecking this option to remove the error.

We also masked your OneDrive password and we do not recommend sharing your password with anyone. If you would like to share the file, please use the built-in sharing feature instead, so that you can share the workbook with other community members:

Share OneDrive files and folders

Regards,

Sheen

The file is on OneDrive ****  The password is ****

[PII is removed by Alex Chen MSFT Support]