How to Remove Comma in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

remove comma in excel

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.

comma 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.selecting general format

The commas will disappear from the numbers.

removed commas in Excel

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.selecting number format 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.

    unchecked thousand separator

The commas will be removed from the numbers.

thousand separator comma removed

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.

number 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.using numbervalue function to remove comma in excel

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.using substitute function to remove comma from numbers

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.

comma between first and last name

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.selecting find and replace
  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.

    find and replace dialog box

  4. Close the dialog box.

The commas will be removed from the names.

comma 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.substitute function to remove comma from names

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

    choose delimited

  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.

    choose comma as delimiter

  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.

    select finish

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

names in separate 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.opening CSV with notepad
  4. The Notepad window will appear. In the Notepad window:
    • Go to the Edit tab > Replace.

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

    replace all comma

All the commas will be removed from the CSV file.

all comma removed from CSV

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.

currency with comma

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.number format box for currency
  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.

    custom format

The commas will be removed accordingly.

comma removed from currency

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.

inverted comma

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.removing inverted comma

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.

thousand separator

To change the comma separator in Excel:

  1. Go to the File tab.
  2. Select More > Options.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.

    changing comma separator

The comma separator will be changed to the decimal point.

comma separator changed

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo