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

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

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.

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.

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.

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

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

Advanced Excel Exercises with Solutions PDF