In Excel, a lot of problems can occur due to the fact that we have not formatted something in the right way, or we need to replace or repair a large amount of data.
For these issues, it is always useful to know some ways in which to operate and correct large data sets.
One of the problems that we can stumble upon is having undesired commas in our files.
Although commas are obligatory for some file formats (as in CSV- Comma Separated Values) we sometimes do not want commas to appear in our data.
We will show different ways to remove commas in Excel, depending on the situation and the text that we are dealing with.
Remove Comma with Formatting
For our example, we will use the table with loans, their amount, duration and date of approval, and banking officers that approved them.
In column B, we have loan amounts in which a thousand values are separated with a comma. This is because this column is formatted as currency. To remove these commas, we have to change the formatting. We will do this by selecting the B column, and by going to Home >> Cells >> Format >> Format Cells:
After that, we will just change the format, currently set as currencies, to General.
This way, we will remove the commas from our numbers, as seen below:
If we would want to create a number with commas or remove them for that matter, we can select column B and go to format again.
When we do it, we will select Number format and on the right side, we will notice that we have a button next to the “Use 1000 separator (,)” that we can select to get insert commas to separate a thousand in our number.
Remove Comma with Find and Replace
We can use a simple Find & Replace option to replace all the commas in text with any sign that we want, in this case, with empty text.
We will replace the commas in our column A with empty text by selecting column A, and then going to the Home tab >> Editing >> Find & Select >> Replace:
We can also use a combination on a keyboard: CTRL + H.
Both of these will get us to the same window:
In this window, we will put “,” as Find what sign, and empty text as Replace with option.
We will click on Replace all and will lose commas in column A (names and last names).
If you do not want to replace all the commas in your range, you can click on “Find Next” to go through every cell in the selection. In this case, all cells will be highlighted one by one, so we can see which one we are changing.
Remove Comma with Formula
We are going to revert the changes in column A to have the commas in it again. In a situation where we would like to keep the original column but have another one without commas, we can use the formula SUBSTITUTE.
Our formula will be in column E, and it will go like this:
The SUBSTITUTE formula has three parameters:
- text– The range in which we want to change the text
- old_text– Existing text that we want to change
- new_text– Text that we want to input instead of the original.
With our formula, we have changed the commas in column A with an empty sign. We will drag our formula to the end of the range to apply it to every cell.
The results are the same as in the example above, with the simple distinction that we still got to keep the original column.
Remove Comma with Text to Columns
There is another neat way to remove commas in our columns and keep our text in different columns.
The first step is to make sure that we have two empty columns (or more, depending on our text) next to the one that we want to work with.
In our case, we will input two columns right to column A.
Now, we will select column A, go to the Data tab, and then on Data tools, we will select Text to Columns.
Text to Columns Wizard that appears has three steps:
In the first step, we choose Delimited and click the Next button.
In the next step, we have to choose a Comma as our Delimiter and click Next.
In the final step, we choose the destination for the first column (in our case, it will be column B).
We can also choose the Column data format and set it to be General, Text, or Date.
When we click Finish, a warning message will appear:
We will click OK since we know that we do not have data in columns B and C. Finally, we have our table set: