How to Remove Comma in Excel

The comma makes texts and numbers easier to read by acting as a separator. But when importing data from other file types, Excel might change decimal points to commas automatically. There are other instances where commas in the texts and numbers may seem unnecessary. So, it’s important to remove those commas.

In this Excel tutorial, you will learn how to remove comma in Excel.

In the following dataset, we have used the SUBSTITUTE function to remove commas from the names.

Here are the 5 cases to remove commas in Excel:

1. Removing Comma from Numbers in Excel

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

3 methods to remove commas from numbers:

1.1 Changing Number Format

In the following image, a comma is seen in the cell. However, the comma doesn’t appear in the formula box. It means the comma is due to the 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 still 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.
   You can also press Ctrl + H keys to open the Format Cells dialog box.
3. The Format Cells dialog box will appear. In the dialog box:
   • Go to the Number tab > Number category.
   • Uncheck the option Use 1000 Separator (,).
   • Press OK.

   

The commas will be removed from the numbers.

1.2 Using NUMBERVALUE Function

The NUMBERVALUE function is used to transform a text string into a number. In the following image, a comma is seen in the cell as well as in the formula box. Moreover, the numbers are left-aligned instead of right, which means they are stored 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 they are also in the proper number format.

However, 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.
   Here, 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.

2. Removing Comma from Text String in Excel

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

The below dataset contains the first name and last name and they are 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

The Find and Replace feature searches for a specific character, word, etc. in the worksheet 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:
   • Insert a comma in the Find what field.
   • Keep the Replace with field blank.
   • 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 comma 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:
   • Choose Delimited.
   • Press Next.

   

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

   

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

   

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

3. Removing Commas from 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:
   • Go to the Edit tab > Replace.

   

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

   

All the commas will be removed from the CSV file.

4. Removing Comma from Currency in Excel

The following dataset contains the months along with the sales amounts. The sales amounts are in the currency format.

To remove commas from currency in Excel:

1. Select the cell range.
2. Go to the Home tab > Number group > Number Format icon.
   You can also 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.

5. Removing Inverted Comma in Excel

In the following dataset, you can see an inverted comma in the formula box although it doesn’t appear in the cell. Cell formatting error occurs 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.
   Here, 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

A Comma is generally used as the thousands separator in most places. 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. However, there are some countries where the decimal point is used as the thousands separator. You can change the thousands separator in Excel as your requirement.

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:
   • Go to the Advanced tab.
   • Uncheck the Use system separators option.
   • Insert a comma in the Decimal separator field.
   • Insert a decimal point in the Thousands separator field.
   • 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

Conclusion

This article showed how to remove commas in Excel. The cases include removing commas from texts, numbers, currencies, and CSV files. Changing the cell format usually removes commas from numbers. Use either the Find and Select feature or the SUBSTITUTE function to remove commas from texts. Leave a comment for any queries.

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