How to stop Excel from auto formatting 6 A to 6:00:00 AM

[dmcgetti]

I am sure this is a stupid simple fix but I am drawing a complete blank....

Every morning I have to fix a 30K line report and there has to be an easy way around it.

Thank you as always!

 

[mystie]

Before entering/filling data into the cell, change the Number format to Text. (By default it's General)

 

[Joe4]

Where is the Report coming from?
Does it come in Excel format already, or some other format and you are importing into Excel?
Does it already come with the column formatted like this?
If so, you will probably need some VBA if you want to automatically fix it.
If you would like help with that, please let us know which column it is, and provide us with some examples of what other information may look like in this column.

 

[dmcgetti]

Before entering/filling data into the cell, change the Number format to Text. (By default it's General)

The report is generated by another program.

 

[dmcgetti]

Where is the Report coming from?
Does it come in Excel format already, or some other format and you are importing into Excel?
Does it already come with the column formatted like this?
If so, you will probably need some VBA if you want to automatically fix it.
If you would like help with that, please let us know which column it is, and provide us with some examples of what other information may look like in this column.

The report is generated by a management system in csv format, when I open the file it is already in the 6:00 AM format.

 

[Joe4]

The report is generated by a management system in csv format, when I open the file it is already in the 6:00 AM format.

Actually, there is a good chance that it really might not be.
One of my biggest pet peeves with Microsoft is that they decided that Excel should be the default program to open CSV files. However, when they do that, they often do implied conversions.
So what you are seeing when viewing the CSV file in Excel may not actually be how the data is stored in CSV!
You need to view the CSV file in a Text editor, such as NotePad or WordPad (or your favorite third party text editor).
If you open that CSV file in one of those programs, what does that field really look like?

If it looks like the way that you need/want it to, you just need to open the CSV file in Excel in a different manner.
If you have ever opened a Text file in Excel, or used Text to Columns, you probably know that you get the Import Wizard, where you can designate the data type of each field. That is what we need to do here, so we can make sure that this field comes in as Text instead of Date or General. They way to do that is as follows:

1. Open up a blank document in Excel
2. Go to the Data menu
3. On the Get External Data ribbon, click on From Text
4. Browse to your file
5. On Step 1, choose the Delimited option
6. On Step 2, choose the comma delimiter
7. On Step 3, move over to the field with these "6 A" entries and choose Text option
8. Click Finish

This should bring the data in and keep the same format you are seeing for this field when viewing in a Text editor.

 

[dmcgetti]

Actually, there is a good chance that it really might not be.
One of my biggest pet peeves with Microsoft is that they decided that Excel should be the default program to open CSV files. However, when they do that, they often do implied conversions.
So what you are seeing when viewing the CSV file in Excel may not actually be how the data is stored in CSV!
You need to view the CSV file in a Text editor, such as NotePad or WordPad (or your favorite third party text editor).
If you open that CSV file in one of those programs, what does that field really look like?

If it looks like the way that you need/want it to, you just need to open the CSV file in Excel in a different manner.
If you have ever opened a Text file in Excel, or used Text to Columns, you probably know that you get the Import Wizard, where you can designate the data type of each field. That is what we need to do here, so we can make sure that this field comes in as Text instead of Date or General. They way to do that is as follows:

1. Open up a blank document in Excel
2. Go to the Data menu
3. On the Get External Data ribbon, click on From Text
4. Browse to your file
5. On Step 1, choose the Delimited option
6. On Step 2, choose the comma delimiter
7. On Step 3, move over to the field with these "6 A" entries and choose Text option
8. Click Finish

This should bring the data in and keep the same format you are seeing for this field when viewing in a Text editor.

Importing as external data worked, thanks!

 

[Joe4]

You are welcome!

One of the first things I do when I get a new computer is change the default programs to open CSV files from Excel to a Text Editor.
It really aggravates me that they choose Excel, when it does those sort of data conversions upon opening it (so it does not really give an accurate depiction of what the data in the file actually looks like)!