How To Remove Comma In Excel – Quick Method In 2023

A comma is a common symbol to work with numbers and text in Excel. A character can be used to serve to separate words (such as one that separates the last and first names as well as an address) or as element of grammar. Although commas can to make the data easier to read, occasionally it is necessary to eliminate all commas from your data (or take out particular examples of commas). In this guide I’ll demonstrate how to remove comma in excel out of numbers or text.

Remove comma from numbers

When working using numbers within Excel You can apply different formats to the cells, and the numbers will be displayed in the appropriate format. Since these commas form part of the formatting and not the actual number We must edit the formatting in order to remove commas in excel from numbers. If you have a data set as follows and you’d like to remove commas in excel in column B. within column B.

Here are the steps to take:

1. Choose your dataset (the one that has the numbers).
2. The Home tab is located in the ribbon
3. In the group with numbers you can click on the launcher for dialogue boxes (the small arrow with a slanted curve in the lower-right corner in the number group)
4. Within the Format Cells dialog box, ensure that the tab titled Number is selected.
5. In the list of Categories choose the number (if not already selected)
6. Select the ‘Use 1000 Separator’ (,)’ option’ and then specify how many decimal numbers you’d like
7. Click OK

The steps above would eliminate the comma in all chosen numbers.

Tips: You could also make use of this key combination Control+1 in order to access the dialogue box (after selecting the cells). Press the Control key, then press the 1 key. Keep in mind that this technique will only work when the comma was part of the formatting , and not the cell itself. To verify this, open the cell and look for the the Formula Bar. If you notice commas appearing in your formula bar it means they are not an element of formatting.

How to remove comma in excel by using the NUMBERVALUE function?

If you are in an instance where numbers aren’t actually figures (but texts) and you need to eliminate the comma from these numbers, you may make use of this number Value program. This function accomplishes exactly that it converts text into numbers.

Imagine you have a set of data as illustrated below in which an individual has entered manually that comma (or it was part of a download). It is evident that these aren’t numbers because they align to the left of the cells (by the default). When you choose an area of the cell and then look at the Formula Bar, you’ll see the commas appearing as component of the formula (indicating that it is not the numeric number, but rather text).

Here is the formula to convert this text into numbers.

=NUMBERVALUE(B2)

Copy this formula to every cell in the column , and you’ll have the numbers.

There is a chance that you will not see the comma appearing in the output from the calculation, however it could be because of the formatting of numbers. It is possible to use the earlier method to eliminate these commas out of the numbers.

Remove commas in excel from text String

The remove commas in excel from text is different from removing the comma from numbers. In texts, the word comma in fact a part of the string. Therefore, you must locate and remove commas in excel in a way.

Let me give you some ways to achieve this, assuming you have text data.

Using find and replace

With the the Find and Replace feature that you can use to locate all the commas within the cell and then replace it with something else (blank or a different character).It is important to note that this method will only work for text data. If there are numbers where the comma is due to formatting, then this method cannot remove commas in excel. Imagine you have the names information as illustrated below, where you will see a space after the first and the final name.

Here are the steps needed to remove commas in excel in the names database:

1. Select the data.
2. Click the Home tab.
3. In the Editing section, select the Find & Replace option.
4. Click on Replace. This will start dialog box called Find and Replace. Find and Replace dialog box.
5. In the “Find what field, enter , (a comma).
6. Make sure to leave the ‘Replace with field:’ empty. If you wish to eliminate the comma in order to substitute it for another character then you must enter it in the “Replace with:” field.
7. Click on the Replace All button.

The steps above would remove commas in excel from the cells that were selected. Be aware that the steps above would eliminate all instances of commas in every cell. If you have more than one comma within an individual cell, all characters would be eliminated. Additionally, this could alter the original data. If you’d like to maintain the original data First, you must create an backup copy or transfer the data to an additional sheet or range, and then follow the steps above.

Removing “Thousand Comma Separators” in excel

The data that has 6 or 7 digit values contain more than one comma than. Therefore, in order to remove commas in excel from either 6 or 7 digit numbers then you must use a different method.

Applying general format

Simply select the values from the Excel sheet, then navigate to Home. Click on the number command, then from the drop-down menu select the General format. Then, you can remove commas in excel and update the column to reflect the result.

Applying the format cells dialog box

To remove commas in excel to create larger amounts (also known as Formatted number Cells) using Methods to Format Cells: To use the Format Cells method First, you must select all rows of values by commas. Then, press the button at end of the command Number.

When the dialog box comes up, it will show the tab Number and Category Untick the Use 1000 Separator (,) checkbox. Select OK to complete the procedure. This process will also show you the most recent result in the column, which previously had commas.

An easy tip: The shortcut to open the Format Cells Dialog box CTRL+1.