how to remove comma in Excel

A comma in excel helps to delimit related values and make numbers more readable. It’s also used in a special file format called CSV that requires you to separate every data item with a comma. 

However important this punctuation mark appears to be, sometimes, you may need to remove all the commas from the data. The work becomes tedious if the cells to be worked on are large files and not just a few cells that you could work on manually.

This tutorial is designed to help you with the necessary steps, in as simple and precise ways as possible to aid you to get this job done as fast and easy as possible. 

There are several methods that you can use to remove commas in Microsft Excel. We will go through the following methods of removing commas in Excel one after the other.

  1. Remove commas in Excel using Find and Replace
  2. Remove comma in Excel using the NUMBERVALUE function
  3. Remove comma in Excel using the SUBSTITUTE formula
  4. Removing commas in Excel from formatted number cells
  5. Removing comma in Excel and separating text into different column 

Removing commas using Find and Replace function

The find and replace functionality helps you to locate all the commas in a given data set and replace them with something else; either leaving it blank or replacing them with a different character. Note that this method only works with text data and not a numerical dataset. 

Let’s say you have this dataset and you are required to remove the commas from the list given to you.

The steps below will guide you through how to take the commas out of the list 

  • Select the list
  • Click on the home tab and locate the editing group (you can also use the keyboard shortcut {ctrl + H } to open the Find and Replace dialogue box
  • Click on the find and replace option
  • Click on replace, this will open the find and replace dialogue box
  • In the ‘Find what’ field center, input a comma (,)
  • Leave the ‘Replace with’ field empty and click on the ‘Replace All’ button

This is the result. 

Remove comma using the NUMBERVALUE function

The NUMBERVALUE function converts texts to numbers, so in case you need to convert some texts which are not really numbers into numbers, the NUMBERVALUE function can be of greater help. 

Below is the syntax for converting texts into numbers.

=NUMBERVALUE(B2)

Paste this formula into the formula bar.

The supposed results are displayed below.

Removing comma using the SUBSTITUTE formula

Another option is to use the SUBSTITUTE function, which allows you to replace the comma with a blank or any other character.

Unlike the Find and Replace approach, which eliminates all commas at once, the SUBSTITUTE function allows you to have a little more discretion (as we will see in the examples later in this section).

Assume you have a dataset like the one below, and you want to get rid of all the commas.

Input this formula into the formula bar =SUBSTITUTE(A2,”,”,””)

The results are shown as 

Three parameters are expected by the formula above.

  1. The text (or a cell reference to the text) from which the comma should be removed

2. The character you’d like to replace (comma in this example)

3. The character that should replace whatever you want to substitute (blank in this example).

Removing commas from formatted number cells

While working with data in Excel, you might apply data types to the cells, which will change how the values are shown.

We must update the format to remove the commas from the digits because they are part of the formatting and not the figure itself.

techpady virtual classroom

Digital School Academy

Join our telegram channel and benefit from our regular digital skills trainings and also become a tech guru with very simplified tech tutorials

Follow the steps below

  • Select the dataset with numbers
  • Click the home button
  • In the number group, click on the dialogue box launcher (the small slanted arrow icon at the bottom-right part of the group)
  • In the Format Cells dialogue box, make sure the Number tab is selected
  • In the Category list, select Number (if not selected already)
  • Uncheck the ‘Use 1000 Separator (,)’ option and specify how many decimal digits you want
  • Click ok to apply the new effects to the numbers

Removing comma and separating text into different column 

If given a dataset where you need to take out the comma and separate the two variables into two different columns, you can use the method below to get the task completed easy peasy.

  • Ensure that there is an empty column next to the one you want to work on. If not, then insert a new one.
  • Select the column of cells you want to work on.
  • From the Data tab, click on the ‘Text to Columns’ button under the ‘Data Tools’ group.
  • This will open the ‘Convert Text to Columns’ wizard. In Step 1 of 3, you will find the ‘Delimited’ option selected by default, so leave it as it is and click on the Next button.
  • This will take you to Step 2 of 3. From the list of Delimiter Options, check the box next to ‘Comma’.
  • Click Next
  • This will take you to Step 3 of 3. Since you have already selected the column you want to work on, you don’t need to do anything in this step.
  • Click Finish.

This is how it is supposed to appear

Conclusion

We covered a variety of scenarios in which you might want to delete commas in this tutorial. In each case, we also demonstrated how to delete the commas. We hope you found this tutorial helpful. Please let us know if you have any alternative methods for removing commas in Excel that you find more convenient. Don’t hesitate to ask your questions if you have any.

Spread the love

Similar Posts

Leave a Reply

Your email address will not be published.