A comma is commonly used when working with both text and numbers in Excel. With text, a comma can be used as a separator (such as a separator between the first and last name or address) or as a part of regular grammar. And with numbers, it often used to make the numbers more readable (a thousand separators is the most common use of comma with numbers). While comma does help make the data a lot more readable, sometimes, you may want to remove all the commas from the data (or remove specific instances of the comma). In this tutorial, I will show you some simple ways to remove comma in Excel from text and numbers. So let’s get started!
Remove Comma from Numbers
When working with numbers in Excel, you can apply various formats to the cells and the numbers would be displayed accordingly. Since these commas are part of the formating and not the number itself, we need to edit the formatting to remove these commas from the numbers. Suppose you have a dataset as shown below and you want to get rid of the commas from the numbers in column B.
Below are the steps to do this: The above steps would remove the comma from all the selected numbers.
Tip: You can also use the keyboard shortcut Control + 1 to open the format cells dialog box (after selecting the cells). Hold the Control key and then press the 1 key. Remember that this method would only work where the comma was a part of the formatting and not the cell. To check this, select the cell and see the value in the formula bar. If you see commas in the formula bar, then these are not part of the formatting.
Using the NUMBERVALUE Function
In case you have a situation where numbers are not actually numbers (but text) and you want to remove the comma from these numbers, you can use the NumberValue function. This function does exactly this – converts text to a number. Suppose you have a dataset as shown below where someone has manually entered the comma (or it came as a part of a data download).
You can see that these are not numbers as they align to the left in the cell (by default). Also, when you select a cell and see in the formula bar, you can see the commas as a part of the number (indicating that this is not numeric but text). Below is the formula that will convert these text into numbers. Copy this formula for all the cells in the column and you will have the numbers.
There is a possibility that you may still see the comma in the result of the formula, but that would then be because of number formatting. You can use the previous method to remove these commas from the numbers.
Remove comma from Text String
Removing comma from text string would work differently than removing comma from numbers. With text strings, the comma is actually a part of the string and you need to find and then remove these commas somehow. Let me show you a couple of methods to do this when you have the text data.
Using Find and Replace
With Find and Replace functionality, you can easily find all the commas in a cell and replace it will something else (blank or some other character). Note that this method will only work with text data. In case you have numbers where the comma is because of the formatting, then this method will not remove the comma. Suppose you have the names data as shown below where there is a comma between the first and the last name.
Below are the steps to remove the comma from this names dataset: The above steps would remove all the commas from all the selected cells.
Note that the above steps would remove all the instances of commas from all the cells. So, if you have more that one comma in a cell, all the commas would be removed. Also, this would change the original dataset. In case you want to keep the original data intact, first, create a backup copy or copy the data to another sheet/range and then use the above steps.
Using SUBSTITUTE Formula
Another way to remove the comma is by using the SUBSTITUTE function, where you can substitute the comma with a blank or any other character. Unlike the Find and Replace method where it removes all the commas in one go, with the SUBSTITUTE function, you get a bit more control (as we will see in the examples later in this section). Suppose you have a dataset as shown below where you want to remove all the commas.
Below is the formula that will do this:
The above formula takes three arguments: But what if you want to remove only the first comma and not the second one. With SUBSTITUTE function, you can specify the number of instances that you want to substitute. This can be done using the fourth argument, which is an optional argument. In case you don’t specify this optional argument, the function will substitute all the instances of the specified character/string. Suppose you have a dataset as shown below and you want to remove only the first comma and not the second one.
Below is the formula that will do this:
The above formula also uses the fourth argument (which is optional) – [instance_num]. Specifying the instance number as 1 tells the formula that it should only substitute the first instance of the comma and leave all the other instances as is. But what if you don’t want to remove the first comma, but instead substitute the second comma with a dash instead. You can do that using the below formula:
The above formula only does the substitution to the second instance of the comma and replaces it will a dash. So these are some simple ways to remove the comma from numbers and text strings in Excel. I hope you found this tutorial useful! Other Excel tutorials you may like:
How to Remove Line Breaks in Excel Change Negative Number to Positive in Excel [Remove Negative Sign] How to Remove the First Character from a String in Excel How to Remove Time from Date/Timestamp in Excel Separate First and Last Name in Excel (Split Names Using Formulas) How To Remove Text Before Or After a Specific Character In Excel
