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