[Fixed!] Excel Not Saving CSV with Commas (7 Possible Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.


[Fixed!] Excel Not Saving CSV with Commas: 7 Possible Solutions

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.

Changing Separator When Saving CSV

  • A dialog box entitled Excel Options will appear.
  • After that, select the Advanced option.

Changing Separator When Saving CSV

  • 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.

Changing Separator When Saving CSV

  • 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.

Changing Separator When Saving CSV

  • 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.

Changing Separator When Saving CSV

Finally, we can say our process worked perfectly and are able to fix the issue of Excel not saving CSV with commas.

Read More: Save Excel as CSV with Double Quotes


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.

Save Your Excel File as ‘CSV UTF-8 (Comma Delimited)’ to Excel not saving CSV with commas

  • 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.

Save Your Excel File as ‘CSV UTF-8 (Comma Delimited)’ to Excel not saving CSV with commas

  • 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.

Save Your Excel File as ‘CSV UTF-8 (Comma Delimited)’ to Excel not saving CSV 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: Set Comma Instead of Semicolon as Delimiter in Excel CSV


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=,’.

Indicate Your Separator Directly to Excel not saving CSV with commas

  • 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.

Indicate Your Separator Directly to Excel not saving CSV with commas

  • 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.

Indicate Your Separator Directly to Excel not saving CSV with commas

  • After that, save the file with the file format CSV(Comma delimited) (*.csv) and click Save.

Indicate Your Separator Directly to Excel not saving CSV with commas

  • Now, if you open the new file with Notepad, you will see that our dataset is saved in the CSV format.

Indicate Your Separator Directly to Excel not saving CSV with commas

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 Save Excel File as CSV with Commas


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:

Choose Delimiter from Text Import Wizard to Excel not saving CSV with commas

📌 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.

Changing Separator When Saving CSV to Excel not saving CSV with commas

  • 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.

Choose Delimiter from Text Import Wizard to Excel not saving CSV with commas

  • 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.

Choose Delimiter from Text Import Wizard to Excel not saving CSV with commas

  • In the second step, select the Delimiters as Semicolon and again click Next.

Choose Delimiter from Text Import Wizard to Excel not saving CSV with commas

  • Then, keep the Column data format in General and finally click Finish.

Choose Delimiter from Text Import Wizard to Excel not saving CSV with commas

  • 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.

Choose Delimiter from Text Import Wizard to Excel not saving CSV with commas

  • 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.

Choose Delimiter from Text Import Wizard to Excel not saving CSV with commas

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 Multiple Excel Files to CSV


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:

Using Power Query to Excel not saving CSV with commas

📌 Steps:

  • To start this process, in the Data tab, select Get Data > From File.
  • Then, choose the From Text/CSV option.

Using Power Query to Excel not saving CSV with commas

  • 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.

Using Power Query to Excel not saving CSV with commas

  • A new sheet titled Text will create automatically and the data will import as a table.

Using Power Query to Excel not saving CSV with commas

  • 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.

Using Power Query to Excel not saving CSV with commas

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 to CSV without Opening


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.

Modifying Regional List Separator

  • 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.

Modifying Regional List Separator

  • Another dialog box called Customize Format will appear.
  • After that, change the List separator to comma (,) and click OK.

Modifying Regional List Separator

  • 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.

Modifying Regional List Separator

  • 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.

Changing Separator When Saving CSV

Thus, we can say our method 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


Download Practice Workbook


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!


Related Articles

<< Go Back to Export Excel to CSV | Export Data from Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo