How to Remove Commas in Excel – 4 Methods

 

 

Method 1 – Converting Commas to Decimal Points to Remove Commas from Numbers

The following dataset contains Numbers with Commas. To convert the commas to decimal points:

how to remove commas in excel


1.1 Using SUBSTITUTE Function

Use the SUBSTITUTE function. It replaces the existing text with new text in a text string.

Steps:

  • Enter the following formula in B5.
=SUBSTITUTE(B5,",",".")+0
  • Press ENTER.

Note:  0 was added after the SUBSTITUTE function to display the Number Format.

Converting Commas to Decimal Point to Remove Commas in Excel

This is the output.

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

Using SUBSTITUTE Function to remove commas in excel

  • Use the AutoFill feature to see the result in the rest of the cells.


1.2 Utilizing the Text to Columns Feature

Use the Text to Columns feature.

Steps:

  • Select the data to apply the Text to Columns feature.
  • Go to the Data tab.
  • Click Text to Columns.

Utilizing Text to Columns Wizard to remove commas in excel

The Convert Text to Columns Wizard will open.

  • Select Fixed width and click Next.

  • Click Next again.

  • Click Advanced.
  • Enter a comma (,) in Decimal separator.
  • Click OK.

  • In Destination, select C5.
  • Click Finish.

This is the output.

Final output to remove commas in excel


1.3 Using the Find & Replace Feature of Excel

Steps:

  • Copy the cells of the Number with Comma column and paste them into the Corrected Format column.

  • Go to the Home tab.
  • Choose Find & Select in Editing.
  • Select Replace.

Using Replace Feature of Excel to remove commas in excel

The Find and Replace dialog box will open.

  • In Find what, enter comma (,) and in Replace with, enter decimal point (.).
  • Click Replace All.

  • Excel will show a message: All done. We made 5 replacements. Click OK.

  • Click Close.

Commas are removed and replaced with a decimal point.

Final output of method 1 to remove commas in excel


Method 2 – Removing Thousands Comma Separators from Numbers

In the following dataset there are numbers which have thousands comma separators.

Removing Thousands Comma Separators from Numbers in Excel


2.1 Applying the General Format

Steps:

  • Copy the cells of the Number with Comma column and paste them into the After Removing Comma Separator column.

Applying General Format to remove commas in excel

  • Select the After Removing Comma Separator column.
  • Go to the Home tab.
  • Click Number and choose General.

This is the output.

final output of method 2.1 to remove commas in excel


2.2 Using the Format Cells Dialog Box

Steps:

  • Copy the cells of the Number with Comma column and paste them into the After Removing Comma Separator column.

 

Using Format Cells Dialogue Box to remove commas in excel

  • Select the After Removing Comma Separator column.
  • Click as shown below.

The Format Cells dialog box will be displayed.

Note: You can press CTRL + 1 to open the Format Cells dialog box.

  • Click Number.
  • Uncheck Use 1000 separator (,).
  • Click OK.

This is the output.

Final output of method 2.2 to remove commas in excel


2.3 Using the NUMBERVALUE Function to Remove Commas

Steps:

  • Use the following formula in C5 and press ENTER.
=NUMBERVALUE(B5)

Applying NUMBERVALUE Formula

  • Drag down the Fill Handle to see the result in the rest of the cells.

Flash Fill to Fill Up Remaining Cells


Method 3 – Deleting Commas in Texts

Use the SUBSTITUTE function and the TRIM function.

3.1 Using the SUBSTITUTE and the TRIM Functions

The following dataset contains Text with Commas and the Target Output.

Deleting Comma from Texts in Excel 

Steps:

  • Enter the following formula, in D5.
=SUBSTITUTE(TRIM(SUBSTITUTE(B5,",",""))," ",", ")

Formula Breakdown

  • SUBSTITUTE(B5,”,”,””) →  replaces all commas with blanks. It converts  “,,,ExcelDemy, Excel,,,,” to “   ExcelDemy Excel    “.
  • TRIM(”   ExcelDemy Excel    “) → returns: “ExcelDemy Excel
  • =SUBSTITUTE(“ExcelDemy Excel”,” “,”, “) → replaces the Spaces with a comma and Space.
  • Output  ExcelDemy, Excel.
  • Press ENTER.

This is the output.

  • Drag down the Fill Handle to see the result in the rest of the cells.

final output of method 3 to remove commas in excel


Method 4 – Removing Commas at the End of Texts in Excel

Use the OR function, the IF function, the RIGHT function, the LEFT function, the TRIM function, and the LEN function.

To remove the comma at the end of the texts:

Removing Comma from End of Texts in Excel

Steps:

  • Use the following formula in 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)={“,”,”.”}). The TRIM function (TRIM(B5)) removes all extra spaces.  RIGHT(returned_text_by_TRIM,1), returns the rightmost character from the trimmed text.  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  “Exceldemy, “, TRUE.
  • value_if_true of the IF function: LEFT(TRIM(B5),LEN(TRIM(B5))-1)
  • We can simplify this formula: LEFT(trimmed_text,length_of_the_trimmed_text-1). It returns the whole trimmed text except for the last character.
  • value_if_false of the IF function: TRIM(B5)
  • Output → ExcelDemy.
  • Press ENTER.

This is the output.

  • Drag down the Fill Handle to see the result in the rest of the cells.

final output of method 4 to remove commas in excel


How to Remove Numbers after a Comma in Excel

Use both the Text to Columns feature and an Excel formula consisting of the LEFT function and the SEARCH function.


Using the LEFT and the SEARCH Functions

Steps:

  • Enter the formula in C5.
=LEFT(B5,SEARCH(",",B5)-1)+0

Formula Breakdown

  • SEARCH(“,”,B5)-1) → returns the position of the comma (,) in the text in B5. The position is 4.
  • The LEFT function, returns the first 3 characters.
  • At the end of the formula, 0 turns the value to a number.
  • Output → 156.
  • Press ENTER.

How to Remove Numbers after Comma in Excel

This is the output.

  • Drag down the Fill Handle to see the result in the rest of the cells.


♦ Utilizing the Text to Columns Wizard

Steps:

  • Follow the steps described above.

Utilizing Text to Columns Wizard to remove commas in excel

  • In the Convert Text to Columns Wizard, choose Delimited and click Next.

  • Check Comma and click Next.

  • Select the column as shown below.
  • Choose Do not import column (skip).
  • Select C5 as destination.
  • Click Finish.

This is the output.


Things to Remember

  • You may use Ctrl+H to open the Find and Replace dialog box.

Frequently Asked Questions

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

Use the REPLACE function.

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

Use the Text to Column feature.

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

The resulting value is not a number. 


Practice Section

Practice here.

practice section to remove commas in excel


Download Practice Workbook

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo