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.

Table of Contents

3 Ways to Remove Commas in Google Sheets

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

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

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

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

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.

The result:

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.

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.

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

Convert the number to plain text.
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.

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

The result:

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.

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.

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.

This will open 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 (#).

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

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:

To retain the original data.
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 '#'")

II. The SUBSTITUTE Function

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:

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

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:

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

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

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:

=SPLIT(B2,",")

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:

=CONCATENATE(C2:F2)

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

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

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.