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.
How to Remove Commas in Excel from CSV File: 4 Ways
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:
- 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.
- 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.
- 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.
- 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.
Similar Readings
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.
- 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.
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).
- 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.
- 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.
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.
- 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.
- 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.
- Finally, when you open the Text file in Notepad, you will see that the commas have been removed.
Download Practice Workbook
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. Thank you!
Related Articles
<< Go Back To Remove Comma in Excel | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
