Soumik Dutta
When we are converting our Excel dataset into the CSV file, sometimes happens that the separator commas don’t come between two texts. In this article, we are going to demonstrate to you 7 distinct approaches to fix the issue called Excel not saving CSV files with commas. If you are also facing any difficulties regarding this issue, then you have come to the right place, Download our practice workbook and follow the guideline to resolve it.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
7 Possible Solutions for Excel Not Saving CSV with Commas
To demonstrate the solution, we consider a dataset of 10 city dwellers. In this dataset, we are showing, their ID, house type, location, and the total number of family members. Our primary focus is to save the dataset in the CSV file with commas.
Solution 1: Changing Separator When Saving CSV
In this process, we will use Excel’s built-in feature to solve the issue of Excel not saving CSV files with commas. The method describes below step by step:
📌 Steps:
- At first, select the File tab from the ribbon.
- Now, go to Options.
- A dialog box entitled Excel Options will appear.
- After that, select the Advanced option.
- Under the Editing Options, uncheck the Use system separator to edit. Then, set the Decimal separator as the Dot (.) and the Thousand separator as the Comma (,).
- Check the Use system separator to prevent further change and click OK.
- After that, again select File > Save As.
- Choose a suitable location to save your file and write down a name for your file, In the file format option, choose CSV(Comma delimited) (*.csv) and click Save.
- Then, close the file and right-click on your mouse on the file, and choose Open with > Notepad.
- At last, the file will open in Notepad and you will get all the text separated with commas.
Finally, we can say our process worked perfectly and are able to fix the issue of Excel not saving CSV with commas.
Read More: Convert Excel to Comma Delimited CSV File (2 Easy Ways)
Solution 2: Open CSV in a Text Editor
You have to open a CSV file with a Text Editor to see the content inside of it. For that, you can use the Notepad text editor. When you will open the file here, you can manually make any kind of modification in this file. If your dataset contains a vast amount of data, don’t do any changes here. It may manipulate your dataset and your original data maybe get lost.
Solution 3: Save Your Excel File as ‘CSV UTF-8 (Comma Delimited)’
It is the easiest process to save your dataset as a CSV file. By using this file extension anyone can easily save his file as a CSV file with a comma separator. The steps of this procedure are given below as follows:
📌 Steps:
- To start the approach, select the File on the left side of the Home tab.
- Now, select the Save As option.
- After that, define your file with a suitable name, and choose your desired location to save the file. Next in the file format box, select the file extension CSV UTF-8 (Comma Delimited) (*csv).
- Click Save to complete the process.
- Now, close the Excel file and right-click on your mouse on the file, and choose Open with > Notepad.
- Finally, the file will open in Notepad and you will get all the text separated with commas.
So, we can say our procedure worked successfully and we are able to fix the issue of Excel not saving CSV with commas.
Read More: How to Convert Multiple Excel Files to CSV (3 Suitable Ways)
Solution 4: Indicate Your Separator Directly
In this case, first, we have to write the text manually in Notepad. When we write it in Notepad, we will define the separator. After that, we will open it through Excel and save for with the CSV notation. The process is explained below:
📌 Steps:
- First, open the Notepad app and write down the following text.
- At the beginning of that text, declare the separator as ‘sep=,’.
- Then, for saving the file with a name according to your desire with the file extension .txt. Keep the Encoding as UTF-8. We set Data as our file name. Click the Save button and close the text file.
- Now, launch Microsoft Excel on your computer and select the File > Open.
- You can also press the ‘Ctrl+O’ keyboard shortcut to open a file with Excel.
- Then, select the text file.
- Excel might give you a warning, ignore it and click Yes.
- You will see all the data set in the worksheet pretty nicely.
- After that, save the file with the file format CSV(Comma delimited) (*.csv) and click Save.
- Now, if you open the new file with Notepad, you will see that our dataset is saved in the CSV format.
Lastly, we can say that our working process worked effectively and we are able to fix the issue of Excel not saving CSV with commas.
Read More: How to Convert Excel to CSV without Opening (4 Easy Methods)
Solution 5: Choose Delimiter from Text Import Wizard
The Text Import Wizard is another option for us to change the character between two texts and further save the file as CSV with a comma separator. If you try to remove the characters manually then it will a laborious work. But this feature of Excel can do it within seconds at the time of file import. Here, using this feature we will open a text file in Excel in which separators are semicolons. Then, save the file with the CSV extension. The technique is shown below:
📌 Steps:
- In the recent version of Excel, the Text Import Wizard is not available in the ribbon of Excel. You have to enable it from the Options.
- To enable Text Import Wizard, select File > Options.
- The Excel Options dialog box will appear.
- Now, in this box, select Data.
- Then, under Show legacy data import wizards, check From Text (Legacy) and click OK.
- After that, press ‘Ctrl+O’ on your keyboard to open a file.
- Select the file titled Text.txt and click Open.
- A dialog box called Text Import Wizard will appear. Choose the Delimited and click Next.
- In the second step, select the Delimiters as Semicolon and again click Next.
- Then, keep the Column data format in General and finally click Finish.
- A new Excel file titled Text will open.
- Now, select FIle > Save As.
- Choose the suitable location to save your file and write down a name for your file, In the file format option, choose CSV(Comma delimited) (*.csv) and click Save.
- Next, close the Excel file and right-click on your mouse on the file, and choose Open with > Notepad.
- Finally, you will see all the text separated with commas, and the file is saved in CSV format.
In the end, we can say our process worked perfectly and are able to fix the issue of Excel not saving CSV with commas.
Read More: How to Convert Excel File to Text File with Comma Delimited (3 Methods)
Solution 6: Using Power Query
We can also import a text file and remove its character trapped between two texts by creating a Power Query. We will use the same text file in this process, which we have used in our earlier method. The steps of this method are given as follows:
📌 Steps:
- To start this process, in the Data tab, select Get Data > From File.
- Then, choose the From Text/CSV option.
- A dialog box called Import Data will appear.
- Now, select the file titled Text and click Import.
- Another dialog box entitled Text.txt will appear.
- Keep the Delimiter as Semicolon and finally click Load.
- A new sheet titled Text will create automatically and the data will import as a table.
- After that, select FIle > Save As.
- Then, choose the suitable location to save your file and write down a name for your file, In the file format option, choose CSV(Comma delimited) (*.csv) and click Save.
- Next, close the Excel file and right-click on your mouse on the file, and choose Open with > Notepad.
- Finally, you will see all the text separated with commas, and the file is saved in CSV format.
In the end, we can say our process worked perfectly and are able to fix the issue of Excel not saving CSV with commas.
Read More:Â How to Convert Excel Files to CSV Automatically (3 Easy Methods)
Solution 7: Modifying Regional List Separator
In this method, we are going to show you how you will bring the commas in your CSV file by changing the default list separator. The steps of this process are given below:
📌 Steps:
- First of all, select the Start menu of your computer.
- Write down Control Panel in the Search bar and select the Control Panel app.
- Now, select the Change date, time, or number format from the Clock and Region option.
- A new dialog box will appear, entitled Region.
- Then, select the Additional settings option.
- Another dialog box called Customize Format will appear.
- After that, change the List separator to comma (,) and click OK.
- Again, click OK to close the Region dialog box.
- Click the Close button on the right corner of the Control Panel.
- Now, in the Excel worksheet, go to File > Save As.
- Write down a suitable name for your file, In the file format option, choose CSV(Comma delimited) (*.csv) and click Save.
- Then, close the file and right-click on your mouse on the file, and choose Open with > Notepad.
- The file will open in Notepad and you will get all the text separated with commas.
Thus, we can say our method worked perfectly and are able to fix the issue of Excel not saving CSV with commas.
Read More:Â Set Comma Instead of Semicolon as Delimiter in Excel CSV
Conclusion
That’s the end of this article. I hope that this will be helpful for you and that you will be able to fix the issue of Excel not saving CSV with commas in Excel. If you have any further queries or recommendations, please share them with us in the comments section below.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
