How to Remove Commas in Excel (4 Easy Methods)

While working in Excel, we often need to remove commas to clean up our data. Because if our data is not in the correct format, we can’t do our desired calculations with the data. We can remove commas manually if our dataset is comparatively smaller. But for a larger dataset, it becomes a nightmare for the user to remove commas manually. Don’t worry! This article is here to help you learn 4 handy tricks so that you can remove commas in Excel in a flash.


Download Practice Workbook


4 Methods to Remove Commas in Excel

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 Point to Remove Commas

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 3 methods to do this.

how to remove commas in excel


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.
=SUBSTITUTE(B5,",",".")+0
  • 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.

Converting Commas to Decimal Point to Remove Commas in Excel

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.

Using SUBSTITUTE Function to remove commas in excel

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

Utilizing Text to Columns Wizard to remove commas in excel

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.

Final output to remove commas in excel


1.3 Using 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.

Using Replace Feature of Excel to remove commas in excel

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.

Final output of method 1 to remove commas in excel

Read More: How to Remove Comma in Excel Using Formula (2 Ways)


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.

Removing Thousands Comma Separators from Numbers in Excel


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.

Applying General Format to remove commas in excel

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

final output of method 2.1 to remove commas in excel


2.2 Using Format Cells Dialogue 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.

Using Format Cells Dialogue Box to remove commas in excel

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

Final output of method 2.2 to remove commas in excel

Read More: How to Remove Comma Between Numbers in Excel


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.

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.

Deleting Comma from Texts in Excel 

Steps:

  • Firstly, enter the following formula, in cell D5.
=SUBSTITUTE(TRIM(SUBSTITUTE(B5,",",""))," ",", ")

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

Formula Breakdown

  • SUBSTITUTE(B5,”,”,””) → This part of the formula replaces all the commas with blanks. So, it converts this text “,,,ExcelDemy, Excel,,,,” to “   ExcelDemy Excel    “.
  • TRIM(”   ExcelDemy Excel    “) → It returns: “ExcelDemy Excel
  • =SUBSTITUTE(“ExcelDemy Excel”,” “,”, “) → This part replaces the Spaces with a comma and Space.
  • Output  ExcelDemy, Excel.
  • 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.

final output of method 3 to remove commas in excel


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.

Removing Comma from End of Texts in Excel

Steps:

  • Firstly, use the following formula in cell C5.
=IF(OR(RIGHT(TRIM(B5),1)={",","."}),LEFT(TRIM(B5),LEN(TRIM(B5))-1),TRIM(B5))

Formula Breakdown

  • logical_test of the IF Function: OR(RIGHT(TRIM(B5),1)={“,”,”.”}). Let’s analyze this part. At first TRIM function (TRIM(B5)) removes all extra spaces from the text. Then RIGHT part of the formula, RIGHT(returned_text_by_TRIM,1), returns the rightmost character from the trimmed text. Finally, OR part of the formula, OR(right_most_character_of_trimmed_text={“,”,”.”}), returns TRUE if the rightmost character is a comma or a period. Returns FALSE, if the rightmost character is not a comma or a period. For our value “Exceldemy, “, it returns TRUE.
  • value_if_true of the IF function: LEFT(TRIM(B5),LEN(TRIM(B5))-1)
  • We can simplify this formula in this way: LEFT(trimmed_text,length_of_the_trimmed_text-1). So, it returns the whole trimmed text except for the last character.
  • value_if_false of the IF function: TRIM(B5)
  • Output → ExcelDemy.
  • 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.

final output of method 4 to remove commas in excel


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.
=LEFT(B5,SEARCH(",",B5)-1)+0

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

Formula Breakdown

  • SEARCH(“,”,B5)-1) → The SEARCH function returns the position of the comma (,) in the text in the cell B5. The position is 4.
  • Using the LEFT function, we are just returning the first 3 characters from the text.
  • At the end of the formula, we are adding 0 to make the return value to a number.
  • Output → 156.
  • Following that, hit ENTER.

How to Remove Numbers after Comma in Excel

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.

Utilizing Text to Columns Wizard to remove commas in excel

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


Practice Section

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

practice section to remove commas in excel


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. To learn more about Excel, you can visit our website, ExcelDemy, a one-stop Excel solution provider. Happy learning!


Related Articles

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo