This method is used to remove commas from numbers where they are not numbers, but text. You will notice all the commas from all your selected cells removed. Unselect the checkbox next to the option "Use 1000 Separator (,)". Under the drop-down list of 'Categories,' click on the Number option.Ħ. Click on the Number tab in the dialog box.ĥ. Select Format Cells from the pop-up menu to display a Format Cells dialog box.Ĥ. Choose the cells that contain the numbers you want to work on.ģ. The second parameter (",") is the text you want to substitute, while the third parameter ("") contains the text you want to substitute with. In the above formula, the first parameter (B2) takes the reference to the cell. 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. Tap the "Enter or Return key" on your keyboardĤ. Select the first blank cell of the blank column and enter the formula: =SUBSTITUTE(B2, "," "")ģ. Create a new blank column next to the one containing your text strings with commas.Ģ. This function gives you more control to specify the number of instances you want to substitute.ġ. You can use the SUBSTITUTE function to remove any commas within your text. If you do not want to replace it with anything, leave it blank.ħ. In the Replace with a field, enter what you want to replace the comma with. Enter a comma (,) in the Find what section.Ħ. On the drop-down list, click on Replace to open the Find and Replace dialog box.ĥ. Click on the option Find and Replace drop-down arrow in the Editing group.Ĥ. Click the Home tab on the main menu ribbon.ģ. First, select the dataset that contains your text.Ģ. 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.ġ. With this method, you can easily find all the commas within your worksheet and replace them with something else.
#COMMAS IN EXCEL FORMULAS HOW TO#
How To Remove The Last Comma In Excel Using Formula.How To Remove Comma Between Names In Excel Using Formula.Applying General Format To Remove “Thousands Comma Separators” From The Numbers.