How to Remove Commas in Excel (4 Easy Methods)

This article provides a detailed guide of how to remove commas in Excel. Here, you’ll see the ways of removing commas from numbers through converting commas to decimals or removing thousands of comma separators, text, and numbers after commas. To do these, we will show the use of SUBSTITUTE, NUMBERVALUE,LEFT and SEARCH functions, Find & Replace feature, Text to Columns wizard, and Format Cells dialog box. 

Generally, we remove comma from text or numerical values because too many commas in a single cell might make the data hard to read. Also, to use certain functions and formulas, there might be a need to remove the comma. You may remove comma from data for other reasons as well. Whatever the reason is, follow the article and enjoy.

How to Remove Commas in Excel: 4 Methods

In this section of the article, we will discuss 4 simple methods to remove commas in Excel.

Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

1. Converting Commas to Decimal Points to Remove Commas from Numbers

In the beginning, we will remove commas in Excel by converting the commas to decimal points. In the following dataset, we have some Number with Comma. Our goal is to convert the commas to decimal points. We will learn 4 methods to do this.

1.1 Using SUBSTITUTE Function

In the first method, we will use the SUBSTITUTE function of Excel. It replaces the existing text with new text in a text string.

Steps:

• Firstly, enter the following formula in cell B5.

• Afterward, press ENTER.

Here, cell B5 represents the cell of the column named Number with Comma.

Note: Here, we have added 0 after the SUBSTITUTE function so that the cell is formatted to Number Format.

Consequently, you will see the following output on your worksheet.

Note: As the number is no longer in Text Format and it is in Number Format currently, it is now right aligned.

• Now, by using Excel’s AutoFill feature, we can obtain the remaining outputs as shown in the following image.

1.2 Utilizing Text to Columns Feature

Utilizing the Text to Columns feature is one of the most effective ways to remove commas and convert them to the decimal point in Excel. Let’s follow the steps mentioned below to do this.

Steps:

• Firstly, select the data where you want to apply the Text to Columns feature.
• Following that, go to the Data tab from Ribbon.
• Next, click on the Text to Columns option.

As a result, the Convert Text to Columns Wizard will open as shown in the image given below.

• Now, select the Fixed width option and click on Next.

• Following that, click on Next again.

• After that, click on the Advanced option.
• Then, type in a comma (,) as the Decimal separator.
• Subsequently, click OK.

• Now, as the destination, select cell C5.
• Finally, click on the Finish button.

Consequently, you will get the following output as demonstrated in the image below.

1.3 Using Find & Replace Feature of Excel

Using the Replace feature of Excel is another efficient way to remove commas and convert them to the decimal point. Let’s follow the steps discussed below.

Steps:

• Firstly, copy the cells of the column named Number with Comma and paste them into the column named Corrected Format.

• Following that, go to the Home tab from Ribbon.
• After that, choose the Find & Select option from the Editing group.
• Then, select the Replace option.

As a result, the Find and Replace dialogue box will open.

• Now, from the Find and Replace dialogue box, in the Find what field, input comma (,) and in the Replace with field, input decimal point (.).
• Afterward, click on Replace All.

• Subsequently, Excel will show a message: All done. We made 5 replacements. Then click on OK.

• Finally, click on the Close option from the Find and Replace dialogue box.

Consequently, you will see that the commas are removed and replaced with a decimal point as shown in the following picture.

2. Removing Thousands of Comma Separators from Numbers

In this section of the article, we will learn how we can remove the thousands of comma separators from the numbers in 2 easy ways. In the following dataset, we have some numbers which have thousands of comma separators in them. We will remove these comma separators from them.

2.1 Applying General Format

By applying the General number format to the cells, we can easily remove the thousands of comma separators. Let’s use the steps discussed below.

Steps:

• Firstly, copy the cells of the column named Number with Comma and paste them into the column named After Removing Comma Separator.

• Following that, select the cells of the column named After Removing Comma Separator.
• Then, go to the Home tab from Ribbon.
• Now, click on the drop-down icon from the Number group and choose the General option from the drop-down.

Consequently, you’ll get the following output on your worksheet.

2.2 Using Format Cells Dialog Box

Using the Format Cells dialogue box is another effective alternative to the 1st method. Let’s follow the steps mentioned below to do this.

Steps:

• Firstly, copy the cells of the column named Number with Comma and paste them into the column named After Removing Comma Separator.

• Following that, select the cells of the column named After Removing Comma Separator.
• Next, click on the marked portion of the Number group.

As a result, the Format Cells dialogue box will open as shown in the following image.

Note: Also, you can just press CTRL + 1 to open the Format Cells dialog box.

• Now, from the Format Cells dialogue box, click on the Number tab.
• Following that, uncheck the box of Use 1000 separator (,).
• Finally, click on OK.

Consequently, the thousands of comma separators will be removed as demonstrated in the image below.

2.3 NUMBERVALUE Function to Remove Comma

Another easy and interesting method to remove comma from numbers is to use the NUMBERVALUE Function. In the following section we will see this method.

Steps:

• We will apply the following formula in cell C5 and press ENTER.

In order to get the result for other cells, we will use the Fill Handle feature and get result like the following image.

3. Deleting Comma from Texts in Excel

In the previous sections, we learned about removing commas from numbers. Now, we will learn how we can remove commas from texts in Excel. To do this, we are going to use the SUBSTITUTE function and the TRIM function of Excel, Find & Replace feature & Text to Columns wizard.

3.1 Using SUBSTITUTE and TRIM Functions

In the following dataset, we have some Text with Commas and our Target Output. We will try to achieve our Target Output by following the steps mentioned below.

Steps:

• Firstly, enter the following formula, in cell D5.

Here, cell B5 refers to the cell of the column named Text with Comma.

• After that, hit ENTER.

As a result, you will get the following output as marked in the following picture.

• Now, use the AutoFill feature of Excel to get the remaining outputs.

4. Removing Comma from End of Texts in Excel

In Excel, can also remove commas from the end of texts. We can do this by using the Replace feature if Excel. But here, we will see another method to do this. In this method, we will use the OR function, the IF function, the RIGHT function, the LEFT function, the TRIM function, and the LEN function.

In the following dataset, we have some Text with Commas at the end of the texts. Our goal is to remove the comma from the end of the texts. Let’s follow the steps mentioned below.

Steps:

• Firstly, use the following formula in cell C5.

• After that, press ENTER.

Consequently, you will get the following output on your worksheet.

• Now, use Excel’s AutoFill option to obtain the rest of the outputs, as showed in the following picture.

How to Remove Numbers after Comma in Excel

While working in Excel, in some cases, you might want to keep the number parts before the comma and want to delete the comma and the numbers after the commas.

In this case, you can use both the Text to Columns feature and an Excel formula consisting of the LEFT function and the SEARCH function.

♦ Using LEFT and SEARCH Functions

First, we will discuss the steps of using the Excel Formula. It is as follows.

Steps:

• Firstly, enter the formula given below, in cell C5.

Here, cell B5 indicates a cell of the column named Number with Comma.

• Following that, hit ENTER.

Consequently, you will have the comma and the numbers after the comma removed as shown in the following image.

• Then, by using the AutoFill option of Excel, we can get the remaining outputs.

♦ Utilizing Text to Columns Wizard

We can also achieve this goal by utilizing the Text to Columns Wizard of Excel. Let’s follow the steps discussed below to do this.

Steps:

• Firstly, use the steps mentioned earlier to obtain the following output.

• After that, from the Convert Text to Columns Wizard, choose the Delimited option and click on Next.

• Following that, check the box of Comma and click on Next.

• Now, select the marked column in the following image.
• Afterward, choose the Do not import column (skip) option.
• Then, select cell C5 as the destination.
• Finally, click on Finish.

As a result, you will get the following outputs on your worksheet as demonstrated in the image below.

Things to Remember

• Before modifying the data, keep a backup copy to prevent lose of the main dataset.
• You may use the Ctrl+H to quickly open Find and Replace dialog box.
• The shortcut to open the Format Cells dialog box is Ctrl+1.

Frequently Asked Questions

1. How do I remove 3 characters from left in Excel?

You can use the REPLACE function to remove 3 characters from left in Excel.

2. How do I separate names in Excel with commas?

Use the Text to Column feature to separate names with commas.

3. What is the disadvantage of using Text to Columns Wizard to remove commas in Excel for numbers?

The resulting value after applying this feature are not numbers. This feature converts numbers into another format other than the number format.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Download Practice Workbook

Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to remove commas in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. Happy learning!