How to Remove Comma in Excel: 5 Methods

Method 1 – Removing Comma from Numbers in Excel

A comma can appear in the numbers as part of the cell formatting. Numbers are stored as text mistakenly if you manually input commas. The reason might be, you might need to remove those commas.

1.1 Changing Number Format

A comma is seen in the cell. The comma doesn’t appear in the formula box. This means the comma is due to cell formatting.

To remove the comma from numbers:

1. Select the cell range of numbers with commas.
2. Go to the Home tab > Number group > Number Format drop-down > General.

The commas will disappear from the numbers.

If the commas persist, follow the below steps to remove them:

1. Select the cell range of numbers with commas.
2. Go to the Home tab > Number group > Number Format icon.
   Press the Ctrl + H keys to open the Format Cells dialog box.
3. The Format Cells dialog box will appear. In the dialog box:
   1. Go to the Number tab > Number category.
   2. Uncheck the option Use 1000 Separator (,).
   3. Press OK.

   

The commas will be removed from the numbers.

1.2 Using NUMBERVALUE Function

The NUMBERVALUE function transforms a text string into a number. In the following image, a comma is seen in the cell and the formula box. The numbers are left-aligned instead of right, storing them as text.

To remove commas using the NUMBERVALUE function:

1. Select a blank cell.
2. Apply the formula: =NUMBERVALUE(C7)
3. Use the Fill Handle to copy the formula to the other cells below.
   The numbers will appear without the commas and are in the proper number format.

If the commas still appear, uncheck the option Use 1000 Separator (,) in the Format Cells dialog box as shown in the previous method.

1.3 Using SUBSTITUTE Function

The SUBSTITUTE function replaces a specified substring within a text string with another substring. To remove commas from numbers using the SUBSTITUTE function:

1. Select a blank cell.
2. Use the formula: =SUBSTITUTE(C7,",","")+0
3. Apply the Fill Handle to copy the formula to the other cells below.
   0 is added to the formula to convert it to a numerical value. The numbers will appear in the proper format and without the commas.

Method 2 – Removing Comma from Text String in Excel

Commas are part of text strings. You have to find those commas in the text to remove them.

The dataset below contains the first and last names, separated by a comma. We’ll remove the comma from the names.

Here are the 3 methods to remove commas from text strings:

2.1 Using Find and Replace Feature

In the worksheet, the Find and Replace feature searches for a specific character, word, etc., and replaces them with another value.

To use the Find and Replace feature to remove commas from texts:

1. Select the cell range containing the texts with commas.
2. Go to the Home tab > Editing group > Find & Select drop-down > Replace.
   You can also use the Ctrl + H keys to get the Find and Replace dialog box.
3. The Find and Replace dialog box will appear. In the dialog box:
   1. Insert a comma in the Find what field.
   2. Keep the Replace with field blank.
   3. Select Replace All.

   

4. Close the dialog box.

The commas will be removed from the names.

2.2 Using SUBSTITUTE Function

To use the SUBSTITUTE function to remove commas from texts:

1. Select a blank cell.
2. Apply the formula: =SUBSTITUTE(B7,",","")
3. Use the Fill Handle to copy the formula to the cells below.
   In this way, you can remove commas in Excel using a formula.

2.3 Using Text to Columns Wizard

The Text to Columns Wizard splits data from a single column to multiple columns based on a delimiter.

To split the names into two separate columns based on the comma using the Text to Columns Wizard:

1. Select the cell range.
2. Go to the Data tab > Data Tools group > Text to Columns.
3. The Convert Text to Columns Wizard – Step 1 of 3 dialog box appears. In the dialog box:
   1. Choose Delimited.
   2. Press Next.

   

4. The Convert Text to Columns Wizard – Step 2 of 3 dialog box appears. In the dialog box:
   1. Select Comma as the Delimiter.
   2. Press Next.

   

5. The Convert Text to Columns Wizard – Step 3 of 3 dialog box appears. In the dialog box:
   1. Select or type the desired Destination to insert the names.
   2. Press Finish.

   

The commas will be removed, and the names will be split into two columns.

Method 3 – Removing Commas from the CSV File

Data in CSV (Comma-separated values) files are usually separated by commas. To remove commas in Excel CSV:

1. Right-click on the CSV file.
2. Select Open with option from the Context Menu.
3. Choose Notepad.
4. The Notepad window will appear. In the Notepad window:
   1. Go to the Edit tab > Replace.

   

5. The Replace dialog box will appear. In the dialog box:
   1. Insert a comma (,) in the Find what field.
   2. Insert a space in the Replace with field.
   3. Press Replace All.

   

All the commas will be removed from the CSV file.

Method 4 – Removing Comma from Currency in Excel

The following dataset contains the months and the sales amounts, which are in currency format.

To remove commas from the currency in Excel:

1. Select the cell range.
2. Go to the Home tab > Number group > Number Format icon.
   Or press Ctrl + H keys to open the Format Cells dialog box.
3. The Format Cells dialog box appears. In the dialog box:
   • Go to the Number tab > Custom category.
   • In the Type field, insert $###0 or remove the comma.
   • Press OK.

   

The commas will be removed accordingly.

Method 5 – Removing Inverted Comma in Excel

The following dataset shows an inverted comma in the formula box, although it doesn’t appear in the cell. Cell formatting errors occur due to those inverted commas.

To remove inverted commas in Excel:

1. Select a blank cell.
2. Use the formula: =SUBSTITUTE(C7," ' "," ")+0
3. Apply the Fill Handle to copy the formula to the other cells below.
   0 is added to the formula to convert it to a numerical value. The numbers will appear in the proper format and without the inverted commas.

How to Change Comma Separator in Excel

The following dataset shows the sales amount where a comma separates the thousands. As this comma is part of the cell formatting, it doesn’t appear in the formula box. There are some countries where the decimal point is used as the thousands separator. You can change the thousands separator in Excel as you require.

To change the comma separator in Excel:

1. Go to the File tab.
2. Select More > Options.
3. The Excel Options dialog box will appear. In the dialog box:
   1. Go to the Advanced tab.
   2. Uncheck the Use system separators option.
   3. Insert a comma in the Decimal separator field.
   4. Insert a decimal point in the Thousands separator field.
   5. Press OK.

   

The comma separator will be changed to the decimal point.

Frequently Asked Questions

1. What is the fastest method to remove a comma from numerical values in Excel?

Changing the number format to General or using the NUMBERVALUE function are usually the fastest methods to remove a comma from numerical values in Excel.

2. What is the easiest method to remove commas from text values?

The Find and Replace feature is the easiest method to remove commas from text values.

Download Practice Workbook

<< Go Back To Data Cleaning in Excel | Learn Excel