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.
Similarly, the address comprises the number and street, city, and county, separated by commas.
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.
For now, assume that we only want to remove commas from the Name field (later we will also look at how to remove all commas in a worksheet).
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.
To do a find and replace, we need to call up the Excel Find and Replace dialog box.
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….
You can also use the keyboard shortcut, Ctrl-H.
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.
We click on the Replace All button and an info message pops up telling us how many replacements were made.
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.
Our Name column will still contain the subscriber names, but now with all commas removed.
The previous process removed commas from cells in a single column.
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.
To select the entire worksheet, click on the square at the top left of the worksheet, at the intersection where the row numbers start and to the left of where the column letters start.
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.
The results are shown above. Notice however, that the Subscription Fees cells, still contain a comma.
This is because the Find & Replace functionality only works for text fields, and the Subscription Fees column is numeric.
In fact, if we attempt a Find & Replace on just the Subscription Fees column, we would get something like the above Excel warning.
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.
SUBSTITUTE
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.
Syntax
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 formula we have entered in cell E2 in our snapshot, is =SUBSTITUTE(A2, “,”, “”).
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.
The result is shown above.
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.
We start by selecting the column we wish to split.
Then, from the Excel menu’s Data tab, we click on the Text to Columns option in the Data Tools group.
This will bring up the Convert Text to Columns Wizard.
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 this step, we set our delimiter by ticking the Comma checkbox in the Delimiters group.
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.
The names in our Name column are ordered as lastname, firstname, and so the wizard will split each name in two.
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.
We start by selecting the entire Subscription Fees column.
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.
Alternatively, you can right mouse click anywhere inside the selected column to bring up the context menu, and then click on the Format Cells… option.
Either way, we should now have the Format Cells dialog box on screen. In the Number tab, the Category field displays among others, a list of all available numeric types.
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.
If instead we select the Number option from the Category list, we are now given the choice of whether we want to enable or disable the thousands separator with the Use 1000 Separator checkbox.
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 a third option available, Custom, that allows us to create our own formatting.
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.
Having selected this option from the dropdown list, we can further edit it inside the Type input field.
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.
The result is now as shown above, with any commas in the Subscription Fees column having been removed.
Conclusion
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.
