Stop Excel from changing cell formatting

Question

Hello all,

Thank you for taking your time to read this.

I'm having an issue with certain number combinations that gets changed to dates when using Excel. One example is 8683-01

This will come up as Jan-83

I'm exporting numbers to CSV from an online source, and case numbers gets automatically changed. How do I stop this? I have turned of proofing and AutoCorrection all together, but still doing this. Cell format changes to Custom from General.

Microsoft Excel for Office 365 MSO (16.0.11929.20298) 3d-bit

Thank you for your expertise.

Jay

Answer 1

Hi Jay,

Thanks for providing the information for us.

We test it in the same Monthly channel version of Excel by these steps:

1. Enter  12/2, 11/1, 6/3, 7/16, 8683-01, 8683-02, 8683-03 in .xlsx file.
2. Save it as .csv file and get data in new workbook from this .csv file.
3. Then we find the data format become like this:

I think this situation and yours changing are because Excel is

preprogrammed, so that it will change the numbers to dates automatically. And you changed the queried data formatting instead of changing the original data type, so you have the "2513966" after changing Format Cells to Text in queried cells. 

However, after we keep the original data in the connected query in Text, then load this part of data. The formatting of data will keep the same and won’t change. Please follow the steps here and try to check the result.

1. Double-Click this Query in the left panel.

2. In the new Query Editor window choose the column which you want to keep the Text formatting. (Like Column1 )

3. Click Replace current-> Close&Load, then the data will keep as previous data type. 

We also tested in the same version 1902 (Build 11328.20392) of Semi-Annual Channel, we didn’t have the same behavior as yours(The data formatting will change after we getting query from same .csv file). So we suggest you try the steps and see the result, please post back if data type won’t keep Text.

Appreciated for your cooperation.

Regards,

Ivy

Answer 2

Hi Jay,

Sorry for this inconvenience caused by this.

Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. That’s the reason the formatting of content will change automatically when you import data. We understand this is very frustrating when you enter something that you don't want changed to a date.

I tested at my end, when I get data from another worksheet which cells are like 11/7,6/17,5/23 and so on. The format will change to date after importing.(5/23/2019) Then we found keep the source data formatting as Text can prevent this situation. You can try this way and see the outcomes:

1. Double-Click the query in Queries&Connections-> Click the column you want to keep the Text formatting->Choose Text.
2. Click the Close&Load. To check the formatting of cells change or not.

 

If not this case, we want to collect more information:

1. Does this issue happen to all the Excel document? Even in the new created worksheet?
2. Did this situation happen before? What have you changed? Like updates?
3. Can you try toOnline Require and check if the issue will be fixed? If not, can you provide the screenshot of Excel version information, please go to File->Account-> Product Information and take a screenshot.

Moreover, I want to share this article with you, you have know detailed information about changing formatting to date in Excel.

 Stop automatically changing numbers to dates

Regards,

Ivy

Answer 3

Hello Ivy,

Thank you for your response

I have tried to change the formatting back to text from the custom one being assigned, which will remove the date format, but change it to another incorrect value. For example: "8783-1" entered will turn into "Jan-83" Changing the format back to text, changes it to "2513966"

To answer your questions:

1. This happens to all Excel documents. Creating a new one or from the exports.
2. This did not happen on my old computer which was running Windows 7 and the following version of Office 365: 16.0.11328.20392
3. I have tried this on 2 different computers with same version of Excel, both having same issue. Tried repair and reinstall.

Thank you,

Answer 4

Hi Jay, 

Have you resolved this issue? Did my suggestion work for you? 

Please feel free to contact us if you need further help. 

Regards

Ivy