This post is going to show you all the ways you can remove commas from your data in Google Sheets.

While it may not happen often, commas can be stuck in unusual areas of your data.

This can happen when you’re importing data from external sources. If you’re importing data from a CSV file and you don’t use the correct delimiter, you could have extra commas in the data.

There are many other scenarios wherein misplaced commas can affect the integrity of your data.

When you run into a situation where you need to remove commas, you need not bother because the spreadsheet has various tools designed to handle this type of problem.

In this post, you will learn how to remove commas from your dataset using the following methods.

• Remove Commas from Text with Find and replace.
• Remove Commas from Numbers with a Custom number format.
• Remove Commas from Text with the SUBSTITUTE Function.
• Remove Commas from Text with the SPLIT Function.
• Remove Commas from Text with the REGEXREPLACE Function.
• Remove Commas from Text with an Apps Script.

Get your copy of the example workbook used in this post and follow along!

Remove Commas from Text with Find and Replace

Find and replace is very useful for finding a certain text and replacing it with another.

In most cases, Find and replace is the go-to option for removing unwanted items that might be spread around in the spreadsheet.

Although Find and replace is a powerful tool, it only works on text or string data types.

This example will use the Find and replace feature to remove commas in the Sizes column.

Follow these steps to use Find and replace to remove your commas.

1. Go to the Edit menu.
2. Select the Find and replace option from the menu.

💡 Tip: You can also use the Ctrl + H keyboard shortcut to open the Find and replace command.

3. Add a comma , in the Find box.

The Replace with input will be left blank as this will cause the commas to be replaced with a blank!

4. Click on the dropdown arrow in the Search option and select Specific range.
5. Use the grid icon in the adjacent box to select a range.
6. Click on Replace all.

Google Sheets will replace all the commas with nothing and a notification about how many instances were replaced will appear at the bottom of the dialogue box.

Press the Done button to close the Find and replace menu.

Remove Commas from Numbers Using a Custom Number Format

Find and replace won’t work with numeric data. Nevertheless, you can remove commas from numbers using a Custom number format.

This example will remove commas from the Quantity column with a Custom number format.

Numbers can be formatted in many ways. The spreadsheet comes with some awesome number formats that you can use such as accounting, percent, financial, and currency formats.

Custom number formats however allow you to extend these options by creating personalized number formats.

Follow these steps to remove the commas from your numbers using a Custom number format.

1. Select the column where you want to apply the format.

2. Go to the Format menu.
3. Choose the Number option.
4. Choose the Custom number format option.

This will open the Custom number formats dialogue box,

#.00

5. Enter the above format string into the text box.
6. Click on Apply.

Now, all the commas have been removed from the numbers in the Quantity column.

Google Sheets saves the three most recently used custom format. If you need to reuse this custom format, you don’t have to go through the process of creating it again.

Go to the Format menu, Numbers option and it’s available to reapply.

Remove Commas from Text with the QUERY Function

The QUERY function is an alternative you can use in place of the Custom number format.

= QUERY ( data, query, [headers] )

The QUERY function uses SQL database syntax to manipulate data in the spreadsheet.

These syntaxes go into the query argument while the data argument points to the data that will be queried.

The headers is an optional argument is used to specify the number of rows with headers in the data and it takes an integer value.

= QUERY ( B2:B6, "FORMAT B '#.00'" )

The above QUERY function syntax will remove the commas and format the numbers accordingly just like the Custom number format has.

Remove Commas from Text with the SUBSTITUTE Function

= SUBSTITUTE ( text_to_speech, search_for, replace_with, [occurrence_number] )

The SUBSTITUTE function works like the Find and replace feature.

You specify the characters you’re looking for and what you want to replace it with.

SUBSTITUTE uses four arguments to carry out a task, although just the first three are required.

• text_to_search is the text or cell containing the text where you want to search for the characters to replace.
• search_for is the characters you’re looking to find.
• replace_with is the characters you want to put in place of the search_for characters.
• [occurrence_number] is used for replacing only a specific occurrence of the characters when it repeats in a word. When you omit this argument you will replace all instances of the characters.

= SUBSTITUTE ( B2, ",", "" )

You can use the above formula to remove the commas from the data in cell B2.

The formula will find and commas and replace them with an empty string thereby removing them.

Unlike Find and replace, you can use the SUBSTITUTE function on numbers although this changes their format to text data types.

⚠️ Warning: You can use the SUBSTITUTE function on numbers but this will change their format to a text data type.

Remove Commas from Text with the SPLIT Function

The SPLIT function is another one among text functions that is useful for removing commas.

It works by splitting text around a delimiter character and placing each split part of the original text in individual cells in the row.

= SPLIT ( text, delimiter, [split_by_each], [remove_empty_text] )

The SPLIT function has four arguments, but only the first two are required.

• text is the text or cell with the contents you want to split.
• delimiter is the character around which you want to split the text. This input must be enclosed in quotes. You can specify more than one delimiter in this argument.

• [split_by_each] is essential when splitting a text with two or more delimiters. This argument allows you to specify if you want to split based on each character or based on the string as a whole.
  • True will split by each character in the delimiter.
  • False will split by the entire delimiter string.

• remove_empty_cells defines how to interpret empty cells.
  • True will remove any empty cells from the output.
  • False will keep any empty cells in the output.
  • This will default as True when omitted.

= SPLIT ( B2, "," )

The above SPLIT formula syntax removes the commas and places each component into separate cells.

You can then combine them into one cell with the CONCATENATE function.

= CONCATENATE ( string1, [string2, …] )

CONCATENATE is a simple function that combines two or more texts. It differs from the CONCAT function because CONCAT can only combine two text items.

= CONCATENATE ( SPLIT ( B2, "," ) )

When you wrap the SPLIT syntax in the CONCATENATE function, the cells are combined back into one but without the commas!

Remove Commas from Text with the REGEXREPLACE Function

You can think of REGEXREPLACE as a more technical variation of the SUBSTITUTE function.

Instead of searching for normal text characters, REGEXREPLACE uses special characters called regular expressions to search for text that matches a pattern given with the regular expression syntax.

= REGEXREPLACE ( text, regular_expression, replacement )

There are three arguments that REGEXREPLACE uses.

• text is the text or cell you want to search.
• regular_expression is the regular expression syntax.
• replacement is the text that will replace all matching cases of the regular_expresssion.

= REGEXREPLACE ( B2, ",", "")

The above formula syntax will remove all the commas from cell B2.

📝 Note: The regular expression used is an exact character match since you only want to replace the commas.

Remove Commas from Text with an Apps Script

Apps script are great for creating customized solutions for your Google Sheets projects.

They are also very handy when it comes to automating repetitive tasks. Since removing commas can easily become repetitive, this task is a prime candidate for creating an app script solution.

💡 Tip: You can learn more about apps scripts here.

Go to the Extensions menu, then choose Apps script from the option to create an apps script.

This will take you to the apps script editor window where you can write your code.

function removeCommas() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selectedRange = ss.getActiveRange();
  var selectedValues = selectedRange.getValues();
  var selectedColumns = selectedRange.getWidth();
  var selectedRows = selectedRange.getHeight();

  for (i = 0; i < selectedRows; i++) {
    for (j = 0; j < selectedColumns; j++) {
      selectedRange.getCell(i + 1, j + 1).setValue(selectedValues[i][j].replace(/,/g, '',));
    };
  };
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Remove')
    .addItem('Remove commas from text', 'removeCommas')
    .addToUi();
};

You can then copy and paste the above syntax into your apps script editor.

Make sure you Save it, then click on the Run button. Then go back to your spreadsheet window and refresh it.

The script creates a new menu called Remove which reflects after the spreadsheet reloads.

The Remove menu has a submenu called Remove commas from text. This is the command that will remove the commas from any range that you select.

📝 Note: This script will only work with string or text data types.

Conclusions

You may not require the removal of commas often, but when you do you will know how to handle the situation.

Most of the methods here describe how you can remove a comma from a text data type.

This is the most likely data type that will require the removal of commas. The SUBSTITUTE function will remove the comma from numbers too but remember to change it to a number with the VALUE function.

When it comes to ease of use the app script solution wins because you don’t have to create any functions or follow any steps. Just select the range and use the custom menu item.

Do you use any of these methods to get rid of commas? Do you know any other tips for removing commas? Let me know in the comments below!