How to Remove Comma in Google Sheets (3 Easy Ways)

In this article, we will take a look at a few different ways to remove the comma (,) from data in Google Sheets.

Commas can be found in unexpected places in a spreadsheet, especially when importing data or if numerical values are in a special format.

Google Sheets itself has a few features that we can use to remove them, but we will go a step further and also look at some functions while we are at it.

3 Ways to Remove Commas in Google Sheets

To show the examples, we will use the following worksheet:

sample worksheet to remove comma from google sheets

This dataset contains both text and numerical data that have commas in them.

1. Using the Find and Replace Feature of Google Sheets

The Find and Replace is a long-standing feature in any spreadsheet application. This can be used to search for any data in the worksheet and replace them with a user-defined value. This includes finding all instances of a comma and replacing them with a blank.

Step 1: Navigate to the Find and Replace option from the Edit tab.

Edit > Find and replace

navigating to the find and replace option from the edit tab in google sheets

You can also use the CTRL+H keyboard shortcut to open the Find and Replace window.

the find and replace window

Step 2: In the Find field of the Find and Replace window, input the comma (,).

input a comma in the find field

Clicking on the Find button at the bottom of the window will cycle through all instances of commas in the current spreadsheet (all worksheets). This will give you an understanding of which cells the application has found commas in.

Step 3: Set the Search condition to “This sheet” if you want to replace commas only in the current worksheet, otherwise, no need for any conditional changes. Then, click on Replace all to remove all commas.

setting the find and replace conditions

The result:

remove all comma using find and replace in google sheets

Click Done to close the Find and Replace window.

As you can see, even though Google Sheets recognized the presence of all the commas, it only replaced the ones in the Products column. This is because of the values of the No. of Customers column is formatted as numbers. See the next section to see how we can remove those commas.

Read More: How to Use Find and Replace in Column in Google Sheets

2. Changing the Format to Remove Commas from Numbers in Google Sheets

You will notice that the default number format of Google Sheets includes a comma at every three digits.

the default number format includes a comma at every three digits

So, if it is a formatting thing, then we can take two approaches to remove this comma:

  1. Convert the number to plain text.
  2. Apply a custom number format excluding commas.

I. Converting Number Format to Plain Text

Step 1: Select the cell that contains the numbers with the comma format.

select all cells containing numbers with comma

Step 2: Navigate to the Format menu in the toolbar. Here, select the Plain text option.

selecting the plain text option

The result:

the numbers have been converted to text

Step 3: As you can see, converting to Plain text did not remove the commas. However, you can now use the Find and Replace method to remove these commas.

using find and replace to remove comma in google sheets animated

It goes without saying that this method converts the number values to text. Thus, this should only be used when the value format of the worksheet does not matter.

Read More: How to Remove Numbers from a String in Google Sheets

II. Applying a Custom Number Format

A better way to remove commas from numerical values is to change the number format itself. We can do this by applying a custom number format.

Step 1: Select the cells you want to remove the commas from numbers.

Step 2: Open the Format menu from the toolbar. Navigate to the bottom of the menu to find the Custom number format option.

navigating to custom number format option from the format menu in google sheets

This will open the Custom number formats window:

the custom number formats window

Step 3: In this window, you’ll see the current number format of the selected cells: #,##0. All we have to do to remove the commas is to set the format to just hash (#).

updating the number format to hash to remove comma in google sheets

Step 4: Click on Apply to apply the formatting on the selected cells.

remove comma in google sheets by applying custom number format

Similar Readings

3. Using Functions to Remove Commas in Google Sheets

There are two reasons why you may want to use functions for any customization in Google Sheets:

  1. To retain the original data.
  2. To have more control over the conditions and customizations.

And to remove the comma from cell data in Google Sheets is no exception.

I. The QUERY Function

First up we have the QUERY function. It is perhaps the most customizable available.

The QUERY function syntax:

QUERY(data, query, [headers])

What we want to do with QUERY is to change the format of the number values to remove the commas using the Format clause. The format condition is the same as the one we used for the custom number format in the previous section.

The QUERY formula is:

=QUERY(C2:C11,"Format C '#'")

using the query function to remove comma from numbers in google sheets


The SUBSTITUTE function is a great way to replace a single value with another.

The function’s syntax:

SUBSTITUTE(text_to_search, search_for, replace_with)

The SUBSTITUTE formula to remove all commas from a string in Google Sheets is:


using substitute function to remove comma from string in google sheets

Note: You can also use SUBSTITUTE to remove commas from a numerical value, however, it will convert the value to a string.

You can directly substitute the SUBSTITUTE function with REGEXREPLACE (even keep the arguments the same!) and get the same result:


using regexreplace function to remove comma from string in google sheets

However, the REGEXREPLACE function is better utilized where we need to search using complex string conditions.

Read More: Use REGEXREPLACE to Replace Multiple Values in Google Sheets

III. Using SPLIT and CONCATENATE Function Combination

The SPLIT function is primarily used to separate strings at every delimiter. Its arguments:

SPLIT(text, delimiter)

Now, if we consider the comma (,) to be the delimiter, the SPLIT formula result will be:


using split function to split strings with the comma delimiter

As you can see, all of the values have been divided into different cells at every comma. This also removes any commas from the string.

Now, let’s use the CONCATENATE function to bring them all together:


using the concatenate function to bring all the split values together

We have just proved that using these two functions we can remove commas from a string, so why not try and combine them?


using split and concatenate function combination to remove comma from strings in google sheets

Read More: How to Remove Characters from a String in Google Sheets (6 Easy Examples)

Final Words

While uncommon, knowing how to remove the comma from Google Sheets may become necessary due to some requirements even if it’s not for aesthetic purposes only. And while the easiest way to remove them is by using the application’s built-in features, functions have more customizable advantages.

Feel free to leave any queries or advice you might have for us In the comments section below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply