30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Remove Commas in Excel (Formula)
By Puneet Gogia (Microsoft MVP)

How to Remove Commas in Excel (Formula)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

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, the simplest way to remove a single comma or multiple commas from a value is to use the SUBSTITUTE or NUMBERVALUE function. With these functions, you can write formulas to get rid of commas.

remove-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 value using double quotation marks with nothing between them (“”).
  5. In the end, hit Enter to get the result.
substitute-to-remove-commas

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

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

SUBSTITUTE always returns text, even when the result looks like a number. If you want a number you can calculate with, add — in front of it:

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

And if you want to replace each comma with a space instead of removing it (like in a list of words), use a space between the quotation marks:

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

This method is perfect when dealing with both numbers and strings. And if you have a number only, you can use the NUMBERVALUE function (Excel 2013 and later).

  1. Enter the function.
  2. Refer to the cell with the value.
  3. Hit Enter to get the result.
numbervalue-to-remove-commas
=NUMBERVALUE(A1)

This function converts a number stored as text into a real number, and it ignores the commas used as thousands separators. It doesn’t remove other characters: if the value has letters or symbols, it returns the #VALUE! error.

NUMBERVALUE uses your computer’s regional settings by default. For a value where the comma is the decimal separator, like “12,5”, tell the function which character is which:

=NUMBERVALUE(A1,",",".")

Both NUMBERVALUE formulas return numbers, so you can use the result in calculations right away.

Remove Commas without a Formula

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

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

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 only need to do it once, as the result doesn’t update like a formula does.

Remove Commas That Come from Number Formatting

Sometimes the commas aren’t in the value at all; they’re the thousands separators of the number format. Find and Replace and the formulas above won’t find them. To remove them:

  1. First of all, select the cells and press Ctrl + 1 to open Format Cells.
  2. After that, on the Number tab, select Number.
  3. In the end, uncheck “Use 1000 Separator (,)” and click OK.

To check which kind of comma you have, select the cell and look at the formula bar. If the commas don’t show there, they come from the format.

Frequently Asked Questions

How do I remove only the first comma?

Use the fourth argument of SUBSTITUTE, which tells it which instance to replace. The following formula removes only the first comma:

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

Why doesn’t SUM add up the result?

Because SUBSTITUTE returns text, and SUM skips text values. Use =–SUBSTITUTE(A1,”,”,””) or NUMBERVALUE to get real numbers.

How do I remove commas and other characters at once?

Nest one SUBSTITUTE inside another, one for each character. The following formula removes commas and dollar signs:

=SUBSTITUTE(SUBSTITUTE(A1,",",""),"$","")
Puneet Gogia

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.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment