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.
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.
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.