Using the semicolon is very common in CSV type of File. But if you are really curious to know how you can use a comma instead of a semicolon, then this article may come in handy for you. In this article, we discuss how you can set the delimiter as a comma instead of a semicolon in a CSV file in Excel using proper explanations.
Overview of CSV Comma Delimited File
Your default delimiter/separator for items in a CSV file may use semicolons (;) or commas (,) depending on the region that Excel is in. Due to the fact that exported data from Affinity employ commas (,) as such default delimiter/separator, this may either result in file upload difficulties or group all the parameter values into column A.
All fields are often separated by commas, whereas all records are typically separated by a complex line of characters. All fields in Excel worksheets with commas are enclosed in double quotes, also known as text qualifiers. For example, a single cell having the colors green, blue, and yellow will be recorded as “green, blue, and, yellow.”
Which Separator Is Used in CSV File?
Microsoft Excel utilizes the List separator specified in the Windows Regional Settings for handling.csv files.
You obtain CSV files that are comma-delimited because, within North America and a few other regions, commas are the standard list separator.
In most European countries, people use a comma as a decimal sign, while they treat a semicolon =to separate items on a list. The output is CSV-semicolon delimited as a result.
Implement one of the methods outlined below to obtain a dataset with a different field delimiter.
How to Save an Excel CSV File with Commas
Changing the default Excel Advanced Editing settings, we can change the delimiter of the CSV file after saving.
Steps
- First, click on the File from the worksheet.
- Then from the starting menu, click on Options.
- Then a new window Excel Options will Open.
- Then click on Advanced.
- After then you make sure that the Use system separators box is unchecked.
- The in the Decimal separator: place period “.”
- In the thousands box place Comma “,”
- And click OK after then
- Then when you want to save files as a CSV file, first go to Export.
- And then click on Change File Type.
- After then from the Other File Type section click on CSV.
- Then from the Save as file explorer window, navigate to your desired location and save the file as CSV.
- After clicking Save, we can see that the file is now saved as a CSV file with a comma delimiter instead of a semicolon.
Read More: How to Save Excel File as CSV with Commas (3 Suitable Methods)
How to Change Delimiter in Excel CSV
In the CSV file, data actually separate through the comma semi-colon, etc. If the user wants to change the existing delimiter from semi-colon to comma, then the following method might come in handy for them.
Steps
- First, click on the File from the worksheet.
- Then from the starting menu, click on Options.
- Then a new window named Excel Options.
- Then click on Advanced.
- After that, you can notice that the Use system separators box is in an unchecked state.
- Then change the Decimal box operator to a period and the Thousands box to a comma.
- And click OK after that.
Read More: Convert Excel to Comma Delimited CSV File (2 Easy Ways)
Below, we presented 3 separate ways to set commas as delimiters instead of semicolons. To avoid any compatibility issues, try using the Excel 365 edition.
1. Using Excel Advanced Editing Options to Set Comma as Delimiter Instead of Semicolon in Excel CSV
Changing the default Excel Advanced Editing settings, we can change the delimiter of the CSV file after saving.
Steps
- First, click on the File from the worksheet.
- Then from the starting menu, click on Options.
- Then a new window named Excel Options.
- Then click on Advanced.
- After that, you can notice that the Use system separators box is in an unchecked state.
- Then change the Decimal box operator to a period and the Thousands box to a comma.
- And click OK after that.
- Now we can see that the CSV file now has the delimiter we designated.
Read More: How to Convert Excel Files to CSV Automatically (3 Easy Methods)
2. Setting Comma As Delimiter While Opening or Importing CSV FIle
While importing or opening a CSV file, we can set the delimiter automatically. The CSV file will then open or show accordingly.
Using Notepad
There are many software or apps available that are capable of View CSV files. Notepad is one of them. Using Notepad, we can open the CSV file and tweak the internal text so that it can show the file as a CSV file with delimited content with commas.
Steps
- In the CSV file, we need to edit the file so that the delimiter will turn out to be a comma.
- To begin with, open the match with the Notepad app.
- In the text body, add the mentioned text at the top of the text box.
- That sep indicates the separator of the CSV file in the file.
- Not to mention changing the semicolons of the file to commas.
- After this, you will notice that the delimiter changed to a comma in the main excel file.
Import Text File
We can import text files using the From Text/CSV option and change the delimiter settings in the process.
Steps
- We can import text files to the Exel sheet by changing the delimiter from semicolon to comma.
- To do this, go to Data > Get & Transform Data > From Text/ CSV.
- Then locate files saved in your computer and then click on them to select the file.
- Click import after that.
- Then a window will open.
- In that window, the source data is now present.
- After that, click on the Load button below to load the newly formatted data in the worksheet.
- The file is now formatted as a CSV file with a comma as a delimiter.
- The final form of the CSV file is the image below.
Read More: How to Convert Excel to CSV without Opening (4 Easy Methods)
Similar Readings
- Save Excel as CSV with Double Quotes (3 Simplest Methods)
- How to Apply Macro to Convert Multiple Excel Files to CSV Files
- How to Convert Multiple Excel Files to CSV (3 Suitable Ways)
- [Fixed!] Excel Not Saving CSV with Commas (7 Possible Solutions)
3. Setting Comma as Delimiter Globally
Through the settings menu, we can set the global list operator as a comma and this will make the delimiter option shift from Semicolon to Comma.
Steps
- In the beginning, search for the Region settings in the Windows search bar. Then click on the settings.
- Then we can see that the region settings open up.
- In the region settings window, click on the Additional date, time, & regional settings under the Related settings.
- Then a separate window will open, and in that window, click on the Region.
- another window Region will open.
- Then click again in the Additional settings.
- In another window, set the, in the List, and then click OK.
- After clicking OK, the new CSV file will look like the below image.
- This is how we managed to set the comma as a delimiter instead of a semicolon in a CSV file in an Excel sheet.
Read More: How to Convert Excel File to Text File with Comma Delimited (3 Methods)
Conclusion
To sum it up, the issue of how we can use commas instead of semicolons in CSV in Excel here with 3 different examples.
For this problem, a workbook is available to download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciated.
