How to Remove Commas in Excel from CSV File (4 Simple Ways)

Looking for ways to know how to remove commas in Excel from CSV File? Data in a CSV file is normally separated using commas. But we can remove them by going through some easy steps. Here, you will find 4 ways to remove commas in Excel from CSV File.


Download Practice Workbook


4 Ways to Remove Commas in Excel from CSV File

Here, we will show you 4 different ways to remove commas in Excel from CSV Files.


1. Importing CSV File into Excel to Remove Commas from CSV File

In the first method, you will find a step-by-step to import a CSV file into Excel to remove commas from that CSV file.

Here, we have a CSV file named as Removing Commas from CSV File. Now, we will show you how you can import this file in Excel by removing commas. Go through the steps given below to do it on your own.

Steps:

  • Firstly, go to the Data tab >> click on Get Data >> click on From File >> select From Text/CSV.

Importing CSV File into Excel to Remove Commas from CSV File

  • Now, the Import Data box will open.
  • Then, select your CSV file. Here, we will select Removing Commas from CSV File named CSV file.
  • Next, click on Import.

Opening Import Data Box to Remove Commas in Excel from CSV Files

  • Now, another box will appear to import the file.
  • After that, click on Transform Data.

  • Then, the CSV file will open in Power Query Editor.

Opening Power Query Editor to Remove Commas in Excel from CSV Files

  • After that, click on Reduced Row >> click on Remove Rows >> select Remove Top Rows.

  • Now, the Remove Top Rows box will open.
  • Next, insert 2 as Number of rows.
  • Then, click on OK.

  • After that, click on Use First Row as Headers.

  • Next, click on Close & Load >> select Close & Load To.

  • Now, the Import Data box will open.
  • Then, turn on Existing worksheet option from Where do you want to put the data?
  • Next, insert Cell B4 into the box.
  • After that, click on OK.

  • Finally, you will import this file in Excel removing commas.


2. Using Find and Replace in Text File

Now, we will show you how to remove commas from a CSV File using Find and Replace in Text File. Follow the steps given below to do it on your own.

Steps:

  • In the beginning, select the CSV File from where you want to remove commas.
  • Here we will select Using Find and Replace in Text File named CSV File and Right-click on it.
  • Then, click on Open with >> select Notepad.

Using Find and Replace in Text File to Remove Commas from CSV File

  • Now, the CSV File will open in Notepad as a Text File.
  • After that, click on Edit >> select Replace.

  • Next, insert comma (,) in the search box and a blank ( ) in the replace box.
  • Then, click on Replace until all the commas are replaced with a blank.

  • Now, when all the commas have been replaced then a box will appear informing Cannot find “,”.
  • After that, click on OK.

  • Thus, you can remove commas from a CSV File using Find and Replace in Text File.

Read More: How to Remove Comma in Excel Using Formula (2 Ways)


3. Use of Text Import Wizard Feature in Excel to Remove Commas

Next, you will find a step-by-step way to remove commas from CSV File using the Text Import Wizard Feature in Excel.

Here are the steps.


Step-01: Adding Text Legacy in Excel

Sometimes, you may not find the Text Import Wizard Feature in Excel. In that case, you have to add the Text Legacy tool from Excel Options. Go through the steps given below to do it on your own.

  • Firstly, click on the File tab.

Adding Text Legacy to Remove Commas in Excel from CSV Files

  • After that, click on Options.

  • Now, the Excel Options box will open.
  • Then, go to the Data tab >> turn on From Text (Legacy).
  • Next, click on OK.

Opening Excel Options Box to Remove Commas in Excel from CSV Files


Step-02: Using Text Import Wizard Feature

Now, we will show you how to use the Text Import Wizard Feature to import a CSV File in Excel and remove commas from it.

  • In the beginning, go to the Data tab >> click on Get Data >> click on Legacy Wizards >> select From Text (Legacy).

Using Text Import Wizard Feature to Remove Commas in Excel from CSV Files

  • Now, the Import Text File box will appear.
  • Then, select the CSV File from where you want to remove commas. Here we will select Use of Text Import Wizard Feature named CSV File.
  • Next, click on Import.

  • After that, the Text Import Wizard box will open.
  • Here, you will see that the Delimited option is already selected.
  • Then, click on Next.

Opening Text Import Wizard Box to Remove Commas in Excel from CSV Files

  • Now, select Comma as Delimiters.
  • Afterward, click on Next.

  • Finally, click on Finish.

  • Now, the Import Data box will open.
  • Then, select Existing worksheet.
  • After that, insert Cell B2 in the box.
  • Next, click on OK.

  • Finally, you will remove commas from the CSV File using the Text Import Wizard Feature in Excel.

  • Additionally, you can format the dataset like the image given below.

How to Remove Commas in Excel from CSV Files Using Text Import Wizard Feature


4. Saving as Text (MS-DOS)(*.txt) File to Remove Commas from CSV File

In the final method, we will show you how to remove commas from CSV File by saving it as Text (MS-DOS)(*.txt) File. Follow the steps given below to do it on your own.

Steps:

  • Firstly, import the CSV File in Excel going through the steps shown in Method1.

Saving as Text (MS-DOS)(*.txt) File to Remove Commas from CSV File

  • Now, click on the File tab.

  • Then, click on Save a Copy.

  • After that, give a name to the file. Here, we will name it How to Remove Commas in Excel from CSV Files.
  • Next, choose the Text (MS-DOS) (*.txt) File from the drop-down.

Selecting File Type to Remove Commas in Excel from CSV Files

  • Then, click on Save.

  • Now, if you have multiple sheets in the Excel workbook a Warning box will appear saying it can only save the active sheet.
  • After that, click on OK to save the active sheet as Text (MS-DOS) (*.txt) file.

Warning Box While Removing Commas in Excel from CSV Files

  • Finally, when you open the Text file in Notepad, you will see that the commas have been removed.


Conclusion

So, in this article, you will find 4 ways to remove commas in Excel from CSV File. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo