You work often on CSV files in Excel and suddenly the CSV uses semicolon instead of comma, or comma instead of a semi colon. This sometimes happens when you have changed computers or if you receive the CSV file from a different source.
Force the CSV to use a comma (or semicolon)
Your computer may be set up to open all CSV’s in Excel without allowing you to change the settings. If this is the case, instead of double clicking the file from outside Excel, rather Open Excel and tell it you are Importing Text. Note for the below you may need to switch back on the legacy import buttons.
As per below, open Excel (without opening the CSV file) and click
- on the DATA ribbon,
- choose GET DATA,
- then LEGACY WIZARDS (if you can’t find this switch it back on here),
- and then FROM TEXT (LEGACY)
The following screen will appear. Note that in the Red box you can see the problem. There are semicolons instead of commas. Make sure you choose the delimited option
In the next screen you can specify that this csv is split by semicolons and you will immediately see that Excel now knows where to split it.
You can now click FINISH and the CSV split with semi colons will appear correctly in columns.