Commas serve multiple purposes when working with spreadsheets. They make numbers more readable. For example, the number 1,452,346 is much clearer than the number 1452346.
Commas also help delimit related values in the same cell. For example, you can have the first and last names separated by a comma.
A special file format called CSV (Comma Separated Values) requires that you separate every data item with commas.
Sometimes, however, you might need to clean your worksheet or certain parts of your worksheet from inessential commas.
If there are just a few cells that you need to work with, you can remove the commas manually. But this becomes tedious with larger files.
In this article, we will see a few cases where you would need to remove commas. We will also show you how you can remove the commas in each case.
Removing Commas Using Find and Replace
Let’s say you have cells in your sheet with comma-separated values as shown below:
You can use Excel’s Find and Replace option to selectively remove commas from the cells.
You can also use it to replace the commas with space (or any other character). Here’s how you can do this:
- If you want to remove commas from the entire sheet, press CTL+A on your keyboard or the select All icon (the small gray icon at the top-left part of the sheet). If you want to remove commas from a specific part of your worksheet, select the group of cells you want to work on. Let us assume you want to work only on the data in Column B.
- From the Home tab, click on the ‘Find and Select’ button under the ‘Editing’ group.
- Select ‘Replace’ from the dropdown menu.
- This will open the Find and Replace dialog box. In the input field next to ‘Find what’ put the comma symbol (since this is what we want to find and replace).
- Now you have two options. If you want to replace the commas with space, then put a space in the input field next to ‘Replace with’. If you just want to remove the commas without replacing it with anything, then leave the field blank.
- Click the “Replace All” button to replace all the commas.
Note: If you want to replace the commas one by one, then click “Find Next” to go through each cell in the selection. Each time a comma is highlighted, you can choose to either remove it by clicking the “Replace” button or click “Find Next” to skip it and move on to the next one.
Using Formula to Remove Commas from Text
The same thing can be done by using a formula.
- Create a new blank column next to the one containing cells with the commas
- Select the first cell of the blank column and insert the formula:
- Press the Return key on your keyboard
- Double click the fill handle on the bottom right corner of the cell to copy the formula to the rest of the column.
What does the formula do?
The SUBSTITUTE function takes three parameters. The first parameter contains the reference to the cell, the second parameter is the text you want to substitute and the third column contains the text you want to substitute with.
In this example, we want the comma symbol (“,”) to be just removed, and be replaced by a space.
That is why we put a space between quotes (“ “). If you don’t want to replace the comma with anything, then put a blank (“”) in the third parameter.
Removing Commas and Separating Text into Different Columns
Suppose you have a list of names with first name and last name separated by a comma.
Ideally, you would want to not just remove the comma, but also separate the text into different columns. Here’s how you can use the ‘Text to Columns’ wizard to do this:
- Ensure that there is an empty column next to the one you want to work on. If not, then insert a new one.
- Select the column of cells you want to work on.
- From the Data tab, click on the ‘Text to Columns’ button under the ‘Data Tools’ group.
- This will open the ‘Convert Text to Columns’ wizard. In Step 1 of 3, you will find the ‘Delimited’ option selected by default, so leave it as it is and click on the Next button.
- This will take you to Step 2 of 3. From the list of Delimiter Options, check the box next to ‘Comma’.
- Click Next
- This will take you to Step 3 of 3. Since you have already selected the column you want to work on, you don’t need to do anything in this step.
- Click Finish.
You will now find that the names are divided into two columns and the commas have been removed.
Removing Commas from Formatted Number Cells
The above methods will not work if your cell has numbers that have been formatted to contain commas.
This is because the commas don’t actually exist in the actual number.
They are just formatted to look that way. For example, consider the numbers in the dataset below:
If you have numbers formatted with commas that you want to remove, here’s what you need to do:
- Select the cells that you want to work on.
- Right-click on the selection and select Format Cells from the popup menu.
- This will open the Format Cells dialog box. Select the ‘Number’ tab.
- Under the list of ‘Categories’, select the ‘Number’ option.
- Unselect the checkbox next to “Use 1000 Separator (,)”.
- Click OK.
This will remove the commas from all your selected cells (provided they are of the Number format)
In this tutorial, we showed different cases in which you might have commas that you want to remove. We also showed methods to remove the commas in each case.
We hope you found this tutorial helpful. Do let us know if there are any other techniques that you find easier to remove commas in Excel.
Other Excel tutorials you may like: