Guide of Removing the comma from Text and Numbers In Excel

Commas serve multiple purposes while working with spreadsheets. We use commas in Excel when working with both text and numbers.

A "comma" can be used as a separator or as part of regular grammar in a text. You can use a comma to delimit values within the same cell. For example, the first and last names can be separated by a comma.

In numbers, a comma is often used to make them more readable it is applicable when writing thousands of digits. For example, the number 1, 432, 675 makes much sense and is clearer than the number 1432675.

Sometimes, you may want to remove commas within your spreadsheet. It can be tiresome to do so manually, especially when working with larger files. In our article, we give you simple steps to follow to remove commas from text and numbers.

Table of Contents

Remove comma from text

Using Find and Replace method

With this method, you can easily find all the commas within your worksheet and replace them with something else. It only applies to text strings meaning that if you have any number where the comma is, then using this method will not give you the intended results.

Below are steps to follow;

1. First, select the dataset that contains your text.

2. Click the Home tab on the main menu ribbon.

3. Click on the option Find and Replace drop-down arrow in the Editing group.

4. On the drop-down list, click on Replace to open the Find and Replace dialog box.

5. Enter a comma (,) in the Find what section.

6. In the Replace with a field, enter what you want to replace the comma with. If you do not want to replace it with anything, leave it blank.

7. Click the Replace All button.

Using the SUBSTITUTE formula method

You can use the SUBSTITUTE function to remove any commas within your text. This function gives you more control to specify the number of instances you want to substitute.

1. Create a new blank column next to the one containing your text strings with commas.

2. Select the first blank cell of the blank column and enter the formula: =SUBSTITUTE(B2, ",," "")

3. Tap the "Enter or Return key" on your keyboard

4. Lastly, double-click the fill handle on the bottom right corner of the cell and drag it down to the other cells to automatically copy the formula.

In the above formula, the first parameter (B2) takes the reference to the cell. The second parameter (",") is the text you want to substitute, while the third parameter ("") contains the text you want to substitute with.

Removing commas from numbers in Excel

1. Choose the cells that contain the numbers you want to work on.

2. Right-click on your selection.

3. Select Format Cells from the pop-up menu to display a Format Cells dialog box.

4. Click on the Number tab in the dialog box.

5. Under the drop-down list of 'Categories,' click on the Number option.

6. Unselect the checkbox next to the option "Use 1000 Separator (,)".

7. Click OK.

You will notice all the commas from all your selected cells removed.

Using NUMBERVALUE Function

This method is used to remove commas from numbers where they are not numbers, but text. The function work by converting the text to a number where the comma is manually entered or appears as part of a data download. It has several advantages including;

It discards additional characters and uses only the first one provided for decimal group separators.
It returns the #VALUE error in case the decimal separator appears more than once in the source text or if the group separator occurs after the decimal separator.
It uses separators from the current locale if decimal separator and group separators are not provided.

Steps

1. Write this formula in all the cells in the column

=NUMBERVALUE(A5)

2. When you copy the formula, you will get the numbers.

Converting Commas To The Decimal Point

In case you import numbers from places like pdf, they may have commas in place of decimals. You can remove them easily by using the following method;

Text to Columns Wizard

Text to Columns Wizard allows you to replace commas with decimal points. To remove commas from numbers using this method, you can follow these steps:

1. Select the data where you want to apply the Text to Columns feature.

2. Go to Data Tab and click on the Data Tools group of commands. Next, click on the Text to Columns command.

3. When the Convert Text Columns Wizard dialogue box appears, choose the Fixed Width radio button then click on the Next button.

Alternatively, you can use the keyboard shortcut to open the Convert Text to Columns wizard by pressing ALT + A + E.

4. Click on the Next button again.

5. When you click on the Advanced command, the Advanced Text Import Settings dialog box appears. Here there are two fields;

Decimal separator: As the decimal separator, choose the comma.

Thousands Separator: Do not change anything here.

6. Click on the OK button.

7. As the destination, select cell B5, then choose the Finish button.

Removing Numbers After Commas

In case you want to keep the number parts before the comma and delete the comma and the numbers after the commas, you can use the following methods;

Excel Formula

In this case, you can apply the formula;

=LEFT(A5,SEARCH(“,”,A5)-1)+0

where;

SEARCH (“,” A5)-1): The Search function returns the position of the comma (,) in the text in cell A5. The position is 4.

The use of the LEFT function helps to return the first 3 characters from the text.

In the end, 0 is added to make the return value to a number.

Steps

1. Write down the formula in cell B5.

2. Use the Fill Handle (+) icon to drag down the formula and generate results for the rest of other cells in the column.

Using Text to Columns Wizard

Steps

1. Select the text with commas and open the Text to Columns wizard. Go to Data Tab> Data Tools >Text to Columns. Delimited is by default selected.