Data formatting can get mixed up when you open a data file with CSV format. In this article, we are going to show you how to avoid formatting change in CSV files in Excel.
If you need to prevent numbers from changing into dates when entering data, please see How to stop excel from changing numbers to dates.
Excel applies formatting updates when opening CSV files, and you won't get a prompt to prevent this. You need to import the data in the CSV file. By importing the data, you will gain the ability to select the data type before parsing it. You do not need to select anything if you have a Microsoft 365 subscription or Excel 2019. The newer versions detect the data automatically.
Microsoft 365 or Excel 2019
Avoiding formatting change on CSV on Microsoft 365 or Excel 2019 is fairly easy.
- Activate the Insert tab in the Ribbon
- Click From Text/CSV in the Get & Transform Data section
- Select your file
- You will see the preview of your data
- Either click Load to import the data
- Or, use Transform button to open Power Query Editor and modify your data before parsing
You will see your data without any changes to formatting.
Excel 2016 – Excel 2010
In previous Excel versions, you can use the Text Import Wizard with text strings. To avoid formatting change on CSV, you first need to set data format manually.
- Activate the Insert tab in the Ribbon
- Click From Text/CSV in the Get & Transform Data section
- Select your file
- Move to the Step 3 by Next. Make sure that comma as delimiter is selected
- In the 3rd step, select the columns where you'd like to keep the formatting
- Mark the Text option as a Column data format
- Click Finish to complete parsing
