How To Add and Remove Commas

Adding and removing commas is very simple, but can be very stressful if there are hundreds of data you want to perform that operation for. Not to worry yourself, in this tutorial I will be showing you step by step on how you can add and remove commas from range of data. Just kindly follow the steps below without skipping anyone.

ADDING COMMAS
Step 1 - For this tutorial, you can use the data in the image below or simply create similar data.
In the Home tab, go to the Editing group and click on the option Find & Select. From the dropdown options displayed, click on Replace

Step 2 - The Find and Replace dialog box will be displayed with the Replace tab selected. Inside the dialog box, in the replace tab section, give a space in the Find What input field then click on the Find All button.

Step 3 - All the places in your worksheet where there is a space will be found and displayed in the dialog box. Then, in the Replace With input field type in comma (,) and give a space (press the space bar on your keyboard). This is because we want to replace the places where there are spaces with a comma and a space (, ). Now click the Replace All button as shown in the image below.

NOTE:

If you don't want to add comma to all the data in the worksheet, simply select the one you want to replace from the list displayed and click on the Replace button.

Step 4 - Excel will replace all the white spaces with a comma and space. A small dialog box will be displayed telling you how many replacements were made. Click the Ok button to take you back to the previous dialog box.

Step 5 - Notice the commas that were successfully added in the worksheet data. Click on the close icon to close the Find and Replace dialog box.

Step 6 - ALL DONE! So that's how you add commas in between text that are required of it.

ADDING COMMAS (ALTERNATIVE)
Sometimes there are more than one method for performing a particular operation in Excel. Below is an alternative way that you can use to add commas in between text.
Step 1 - Click on the cell where you want to display the new formatted data (usually by the side as seen in the image below). Now type in =SUBSTITUTE(TRIM(A1)," ",", ").
The SUBSTITUTE takes in three values, first is the string or text to collect extract something from, second is what is to be extracted from the string or text and the last is what is to replace that which is extracted. The TRIM function is used to normalize spacing in text.
Now press Enter on your keyboard.

TIP:

In a nut shell, we replaced spacing in the text with a comma and another space using the SUBSTITUTE function in collaboration with the TRIM function that normalize the spacing around the text or string.

Step 2 - Comma sign will be add successfully as shown in the image below.

Step 3 - Now instead of typing in the same functions for the others, we are going to flash fill the rest. Place your mouse point at the bottom right edge of the cell and you will notice that your mouse pointer will be changed to a plus sign.

Step 4 - So immediately your mouse pointer changes to a plus sign, click and drag down to other cells as shown in the image below.

Step 5 - Other cells will be auto-filled successfully with the correct changes.

Step 6 - ALL DONE!

NOTE:

Before deleting the original text, copy the formatted one with commas, and use the Paste Values option while pasting it on another place. Then you can now delete the original text which will automatically delete the formatted one too that is connected to it.

So now we have known how to add commas to text, so how do we remove commas in between texts also. Simply follow the steps below and learn this.

REMOVING COMMAS
Step 1 - For this tutorial, you can use the previous formatted data or simply create another one as seen in the image below.

Step 2 - In the Home go to the Editing group and click on the Find & Select option. From the dropdown list of options displayed click on Replace

Step 3 - The Find and Replace dialog box will be displayed with the Replace tab selected. In the replace tab section in the dialog box, type in comma in the Find What input field. Then click on the Find All button.

Step 4 - The cell which commas are found will be displayed. Leave the Replace with input field empty and click on the Replace All button.

NOTE:

If you don't want remove comma in all the data in the worksheet, simply select the one you want to remove from the list displayed and click on the Replace button.

Step 5 - Excel will replace remove all the commas. A small dialog box will be displayed telling you how many replacements were made. Click the Ok button to take you back to the previous dialog box.

Step 6 - Notice the commas that are removed in the worksheet data. Click on the close icon to close the Find and Replace dialog box.

Step 7 - ALL DONE!

REMOVING COMMAS (ALTERNATIVE)
Step 1 - Click on the cell that you want to display the formatted data, then type in =SUBSTITUTE( as shown in the image below. The SUBSTITUTE function is used to replace a text or string from another text or words.

Step 2 - Next is to type in TRIM(. TRIM function is used normalize spacing in text.

Step 3 - Type in the cell address containing the data. In this case its A1 as shown in the image below.

Step 4 - Add the closing TRIM parenthesis as shown in the image below.

NOTE:

If you skip any closing parenthesis in functions, that function will likely not work properly or not work at all.

Step 5 - Add a comma sign as shown in the image below.

Step 6 - Next is to input what we are going to be removing from the text in the cell A1. In between double quotation marks, type in comma sign and give a space.

Step 7 - Add another comma sign again as shown in the image below.

Step 8 - Next is to input what we are going to be replacing the comma and space with from the text in the cell A1. In this case we are removing it completely so we replace it with an empty space. In between double quotation marks, type give a space.

Step 9 - Type in the closing SUBSTITUTE parenthesis and press Enter on your keyboard.

Step 10 - Now the new formatted data will be displayed with the comma sign successfully removed.

Step 11 - Now instead of repeating the same functions for all other cell, we are going to flash fill it. Move your mouse pointer to the bottom right edge of the cell and you will notice that your mouse pointer changes to a plus symbol.