To remove commas from a cell, use =SUBSTITUTE(A1,",","") — the empty quotes at the end mean “replace it with nothing”, and SUBSTITUTE removes every comma in the cell, not just the first. To swap them for spaces instead, put a space between the final quotes: =SUBSTITUTE(A1,","," ").
If the value is a number that has come in as text with thousands separators — “1,234,567” left-aligned in the cell — use =NUMBERVALUE(A1), which strips the separators and returns a real number you can calculate with.
For a one-off cleanup with no formula, select the range, press Ctrl+H, type a comma in “Find what”, leave “Replace with” empty, and click Replace All.
One distinction worth making before you start: if the commas are only displayed by a thousands-separator format rather than typed into the cell, there’s nothing to remove — select the cells, press Ctrl+1, and choose Number with the “Use 1000 Separator” box unticked. A formula can’t remove formatting.
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:
- Enter SUBSTITUTE in a cell.
- Refer to the cell with the value from where you want to remove the commas.
- Specify a comma using double quotation marks.
- Again, specify a blank space using double quotation marks.
- 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.
- Enter the function.
- Refer to the cell with the value.
- 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.
- Select the range of cells where you have the values with commas and open the Find and Replace by using Control + H.
- In “Find What”, enter a comma.
- But leave the “Replace with” blank.
- 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.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.