How to Remove Commas in Excel (Formula)

In Excel, a simple and best way to remove a single comma or multiple commas from a value is to use the SUBSTITUTE/NUMBERVALUE. With these functions, you can write formulas to get rid of commas.

Formulas to Remove Commas from a Value

You can use the below steps to write this formula:

1. Enter SUBSTITUTE in a cell.
2. Refer to the cell with the value from where you want to remove the commas.
3. Specify a comma using double quotation marks.
4. Again, specify a blank space using double quotation marks.
5. In the end, hit enter to get the result.

As you can see in the above example, you had a comma after each number in cell A1. But with the substitute, the result that you have got has only numbers without commas.

=SUBSTITUTE(A1,",","")

This method is perfect when dealing with both numbers and strings. And if you have a number only, in that case, you can use the NUMERICVALUE function.

1. Enter the function.
2. Refer to the cell with the value.
3. Hit enter to get the result.

=NUMBERVALUE(A1)

This function converts the value to a number and removes unwanted commas and other characters.

Remove Commas without a Formula

And if you don’t want to use a formula, you can use the find and replace option.

1. Select the range of cells where you have the values with commas and open the Find and Replace by using Control + H.
2. In “Find What”, enter a comma.
3. But leave the “Replace with” blank.
4. In the end, click on “Replace All”.

The moment you click “Replace All”, it replaces all the commas from the selected cells with a blank value (meaning nothing), and you remove all the commas. This method is useful when you don’t need to do it once.