We all know that Excel can be a powerful tool, however in an effort to anticipate the need of a user, Excel can also make it difficult to create spreadsheets for importing data into Acctivate QuickBooks Inventory Software. You may have entered a string of numbers in Excel only to find they were reformatted to a date or something else and no longer display what you intended. Importing or updating records in a database most often the data must match 'exactly'. Therefore when Excel changes the format of your import data your import will fail. This can be extremely frustrating but do not worry you have a few options to easily correct it.
The Apostrophe Method
By placing an Apostrophe ( ' ) preceding the number, you will prevent Excel from reformatting the number and display the number exactly how you have it entered.
Example:
- Entering '1/2 into a cell it will prevent Excel from making your fraction a date.
This method means that even if someone were to change the cell formatting back to General and tries to edit a cell it will continue to look the same instead of being auto-formatted by Excel.
- Type an Apostrophe in the Cell
- Enter the number as you want it to display and press Enter
- Now the numbers should display and import correctly
- This will allow you use MATCH and VLOOKUP functions in Excel (Apostrophe will be ignored)
Format as Text Method
The second most common method to simply format the entire worksheet or a group of cells as Text before you enter your data. Formatting cells to display as text will make Excel display the data exactly as it has been entered. (Remember to format your cells Before you input your data)
- Select the entire sheet or a group of cells
- Next you can either 'Right Click' within the cells you have selected or select the cell format drop down from the Home tab.
- Select Text as the format method for your cells
- Input your data and it will display exactly as entered
In this article, we will learn to stop excel from auto formatting dates in CSV. CSV stands for comma-separated values. Sometimes, we need to open or import a CSV file in our excel worksheet. Excel auto formats the date when we open or import any CSV file. Often, it can be undesirable because it changes the data. So, to stop excel from auto formatting dates in CSV, we need to follow some methods. Today, we will demonstrate these methods in easy steps.
Download Practice Book
Download the practice book.
What Is a CSV File?
The file which separates values by a comma is called a CSV file. It is a delimited text file. Each line of a CSV file consists of a data record where each field is separated by a comma. We can import or export a CSV file that contains data records in our excel worksheet very easily.
3 Ways to Stop Excel from Auto Formatting Dates in CSV
To explain the methods, we will use a CSV file that contains information about the ID Number and Date of Birth of some employees.
If we open or import this CSV file in excel, we will see a dataset like below.
And after auto-fitting the columns, we will see the result like below.
Here, if we look at the Date of Birth column, we can see excel has auto formatted the dates. It does not match with our original dataset in the CSV file. For example, if we look at Cell C2, it contains 10-Jul-93. But our CSV file contains 10-July-1993. We want to display the CSV file without auto formatting the dates. The methods below will discuss how to stop excel from auto formatting dates in CSV.
1. Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates
We can stop excel from auto formatting dates by saving the CSV file as a Text Document file. It is one of the quickest processes. Let’s follow the steps below to learn this method.
STEPS:
- In the first place, open the CSV file.
- Secondly, click on the File tab. A drop-down menu will occur.
- Select Save As from the drop-down menu. The Save As window will appear.
- Now, change the Save as type to Text Documents(*.txt).
- After that, click Save to proceed.
- After saving the CSV file as a Text Document file, open Excel.
- Then, go to the File tab.
- Select Open and then, Browse like the picture below.
- After that, select the Text Document file and click Open. Make sure you have selected All Files in the file type field.
- After clicking Open, you will see a Text Import Wizard on the screen. It is the first step of the Text Import Wizard.
- Select Delimited in the Original data type field and check the My data has headers option. Then, select Next.
- After clicking Next, the second step of the Text Import Wizard will occur.
- Check Comma and uncheck Tab in the Delimiters field.
- Then, click Next.
- After that, the third step of the Text Import Wizard will appear.
- Now, select the desired column from the Data Preview field. We have selected the Date of Birth column.
- After selecting the column, select Text in the Column data format field.
- Finally, click Finish to see the results below.
Read More: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
2. Stop Excel from Auto Formatting Dates by Importing the CSV File
There is another quick way to stop excel from auto formatting dates in CSV files. In this method, we will use the Data tab to import the file. Here, we will use the same CSV file. Let’s pay attention to the steps below to learn more.
STEPS:
- In the beginning, go to the Data tab and select From Text/CSV. It will open the Import Data window.
- Select the CSV file and click Import. Make sure you have selected All Files in the file type field.
- After that, a window containing the dataset will occur. Select Transform Data from there.
- After clicking Transform Data, the Power Query Editor will open.
- Next, select the Date of Birth column in the Power Query Editor window.
- Then, go to the Transform tab and select Data Type. A drop-down menu will occur.
- Select Text from the drop-down menu.
- Instantly, a Change Column Type dialog box will pop up. Select Replace current from there.
- Then, you will find results like below in the Power Query Editor.
- Now, go to the Home tab and select Close & Load.
- Finally, you will see results like below in the excel worksheet.
Read More: How to Use Formula to Change Date Format in Excel (5 Methods)
Similar Readings:
- How to Convert Text to Date with Excel VBA (5 Ways)
- Convert Date to Year in Excel (3 Quick Ways)
- How to Convert Date to Day of Week in Excel (8 Methods)
- Get the First Day of the Current Month in Excel (3 Methods)
- Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
3. Format the CSV File to Stop Excel from Auto Formatting Dates
In this method, we will format the CSV file to stop excel from auto formatting dates. We will mainly change the data format inside the CSV file. Again, we will use the same CSV file here. Let’s observe the steps below to learn more.
STEPS:
- Firstly, open the CSV file.
- Secondly, write:
"John","100","=""10-July-1993"""
Instead of:
"John","100","10-July-1993"
Here, we have added “=” as prefix and “” as a suffix in the Date of Birth column.
- Follow this format for all data entries.
- Save it by pressing Ctrl + S and then, close the Notepad after saving it.
- Next, open Excel and select the File tab.
- Click Open and then, click Browse.
- After that, select the CSV file and click Open. Make sure you have selected All Files in the file type field.
- Finally, you will see results like below.
Related Content: How to Change Default Date Format from US to UK in Excel (3 Ways)
Things to Remember
There are certain things you need to remember to stop excel from auto formatting dates.
- While opening or importing the CSV file from excel, if you don’t find the file in the desired folder, select All Files instead of All Excel Files.
- You need to enable the Power Query if you are using Excel 2013 or earlier versions.
- If you have a large dataset, use Method-1 or Method-2. Method-3 is not appreciated for large datasets.
Conclusion
We have discussed 3 quick and easy methods to stop excel from auto formatting dates in CSV files. I hope these methods will help you to solve your problems related to auto formatting dates in CSV. Furthermore, we have also added the practice book at the beginning of the article. You can download the practice book to exercise. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.
Related Articles
- Last Business Day of the Month in Excel (9 Examples)
- How to Calculate First Day of Previous Month in Excel (2 Methods)
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- Get First Day of Month from Month Name in Excel (3 Ways)