There are several ways to Remove Commas in Excel from Numbers or Text.
A comma is a separator used to give structure and meaning to both text and numbers.
A very large number is easier to read with commas. For example, consider the number 456290713.
To read it, you visually try and break down the number into groups of three digits, going from right to left, to determine if the number is in the millions, billions, trillions, etc.
If we add commas, the number which now becomes 456,290,713, can now be read without hesitation.
In a text document, a comma can help give the correct meaning to a sentence.
Consider the phrase “Let’s eat, Grandma”.
Without the comma, this has a totally different, cannibalistic intent.
However, in a spreadsheet, there may be times when a comma serves no purpose, or is just simply not desirable.
And in this article, we are going to take a look at a few different ways that you can use to remove them.
Here’s How to Remove Commas in Excel from Numbers or Text:
Excel basically has three data types: text, number, and boolean.
Commas cannot be used with the boolean type so is not considered here.
For our purposes, we will be using the following worksheet which contains a list of subscribers.
For each subscriber, the list includes their name, address, and yearly subscription fee.
The subscriber name is made up of the last name and first name, separated by a comma.
Finally, the subscription fee is a dollar value amount with a comma for the thousands separator.
We will look at how to remove all commas from both the text columns and the numeric column.
Start by selecting a single cell, a range of cells, or the entire column, depending on which cells you want to remove the commas from.
The method we are going to use involves nothing more than doing a find and replace.
We are going to ask Excel to look for any commas in the cells we have selected, and replace them with anything we want.
In this case, we are going to replace them with nothing, a blank.
In the Home tab, on the far right in the Editing group, click on the magnifying glass icon or the Find & Select option, and from the context menu that pops up, select Replace….
In either case, you should now have the Find and Replace dialog box on screen.
This is a fairly straightforward search and replace, so we only need to concern ourselves with the Find what and Replace with input fields.
In the Find what input field, we enter a single comma, and in the Replace with input field we only to ensure it is blank.
Assuming all went well, we can click the OK button to close the message, and then the Close button to close the Find and Replace dialog box.
The same process can be used to remove commas from both the Name and Address columns.
All we need to do is select both columns before invoking the Find and Replace dialog box.
The Find what and Replace with input fields would be exactly the same.
In fact, we can do this for the entire worksheet.
This will select the entire worksheet.
You can also use the keyboard shortcut, Ctrl-A.
As before, you only need to now bring up the Find and Replace dialog box and perform the replacement.
This is because the Find & Replace functionality only works for text fields, and the Subscription Fees column is numeric.
To remove the commas from numeric fields, we need to change the cell formatting by invoking the Format Cells dialog box.
Before we do this though, we are going to take a look at another couple of ways of removing commas in text fields.
The method described above, leaves the original column intact.
We are now going to look at two methods, one that uses a formula and outputs the results into a new column, and another that splits the original column into separate columns.
The first method we will look at, using a formula, relies on the Excel function, SUBSTITUTE.
Its definition is given below.
Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
SUBSTITUTE(text, old_text, new_text, [instance_num])
The SUBSTITUTE function syntax has the following arguments:
- Text (Required). The text or the reference to a cell containing text for which you want to substitute characters.
- Old_text (Required). The text you want to replace.
- New_text (Required). The text you want to replace old_text with.
- Instance_num (Optional). Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
For this example, we will concentrate on the Name column in our worksheet, but the same principle can be used for the address column or on any other text based column.
The first parameter in our function call, A2, is a relative reference to the source cell which contains the subscriber’s name, including the comma.
The second parameter, “,” is the string we are looking for in the name contained in cell A2.
The third parameter is simply a blank, denoted by the two double quotes, “”, and is what we replace the second parameter with, if found in cell A2.
Cell E2 now contains the original name but with the comma removed.
We can now use the fill handle (small square in the bottom right hand corner of the bounding box around cell E2) to propagate the formula to the rest of the cells in the column, You can grab and drag the fill handle down the column, or double click it to fill the entire column.
We will now look at the second method, which splits the original Name column into two separate columns.
The Name column is a good candidate here.
It makes sense to want to split each name into last name and first name, and at the same time we also want the comma removed.
Then, from the Excel menu’s Data tab, we click on the Text to Columns option in the Data Tools group.
The first of three steps involves choosing whether our names have a fixed width or use a delimiter such as a comma or a tab. In our case, it is a comma so we select the Delimited radio button, and click the Next button to go to step 2.
In the Data Preview section, we can see if our settings have yielded the correct results before proceeding to the next step.
We once again click on the Next button to proceed to the final step.
Ordinarily, it would overwrite the contents of the original Name column (i.e. column A) with lastname, and write firstname into the immediately adjacent column (column B).
The Destination field in the wizard will therefore display by default =$A$1, which references the original Name column, but we can change this so that it prints our names in whatever columns we want, by setting a new destination reference.
In our case, we have opted to set a destination reference of $E$1, and the result (shown above), is that we leave the original Name column intact, while a copy of the last names and first names are written to columns E and F respectively.
It should be noted that columns B to D, which contain the Address, State, and Subscription Fees columns, were hidden to make any explanation less cluttered.
We are now ready to see how to remove commas from numeric values.
As we saw earlier, Find & Replace only works on text cells.
To remove commas from numeric cells, we need to change their format.
Then, under the Home tab, in the bottom right hand corner of the Number group is the small launch arrow.
Clicking on the arrow will open the Format Cells dialog box.
The Subscription Fees column being a monetary field, it would make sense to choose the Currency option here.
However, as we can see from the snapshot above, the Currency option only allows us to select the currency symbol and the number of places after the decimal point.
There’s no option to disable the thousands separator.
We can also choose how many decimal places to use, as well as how negative values should be displayed.
There is one drawback here though.
What if we had wanted to display the currency symbol next to the dollar amount.
This option was available when we selected the Currency type, but is not available with the Number type.
There is only one field here, Type, with a list of ready custom types we can use as is, or as a starting point to create our own.
Scrolling down the list, we come across the format $#,##0.00;-$#,##0.00 which is two formats in one.
The first part is for positive amounts, and the second, after the semi colon, for negative amounts.
Both have the dollar symbol at the front, and a comma as the thousands separator.
The hash characters (#) followed be zeros (0.00), indicate how many decimal places are to be used, and where the thousands separator should appear.
In the case of a negative value amount, the minus sign is placed at the front, before the dollar symbol.
Since our aim was to remove any commas, our format now becomes $###0.00;-$###0.00.
We could also remove the dollar symbols, or use any other currency symbol.
Excel is an extremely powerful and versatile spreadsheet application.
Even with something as simple as Removing Commas in Excel from Number or Text, there are a number of different methods and approaches available to the user.
Each approach has its pros and cons, but it does give the user the ability to decide which is the most appropriate for the case in hand and the data they have to work with.