What Is Comma In Google Sheets?
The Comma in Google Sheets is a punctuation mark that is strategically placed in textual values as required or to display numeric values according to the Indian or International system for currency.
Users can use the Google Sheets Comma option when working with financial and accounting figures to make them understandable and interpretable in a large dataset. For example, we have an empty worksheet, select any cell, here cell A1, double-click to enter Edit mode (only if there is existing cell value) or just press the comma key on the keyboard, to get the following result.
The output is shown above, the comma placed at the start of the cell as it is considered as a text value. We can further change the formatting styles, alignment, etc.
Key Takeaways
- The Comma in Google Sheets is an option that enables one to insert commas for numeric currency values as a 1000s separator, with the default two decimal places, when we use the format method or the shortcut keys. Also, for textual values as required.
- Users can use the Google Sheets Comma option with positive and negative numbers and number formats, such as Accounting and Currency, to make them more readable.
- The shortcut keys to apply the default number format, which includes commas, to the selected cells are “Ctrl + Shift + 1“, which will insert Commas as a 1000s separator, with the default two decimal places.
- We can add commas using the Ampersand (&) symbol and the TEXT, TEXTJOIN, CONCATENATE functions. With the help of the SUBSTITUTE function, we can either insert comma or replace with anything else.
How To Add A Comma In Google Sheets?
We can add a Comma in Google Sheets in various ways, namely,
- Manually inserting a comma.
- Using the Format tab.
- Using the formulas.
Method #1 – Manually inserting a comma.
Step 1: Select the cell, double-click to enter Edit mode and place the cursor where we want the comma.
Step 2: Press the comma “,” key on the keyboard.
Method #2 – Using the Format tab. [ Note: This method is used for numeric values.]
Step 1: Select the cell with currency or numeric values.
Step 2: Select the “Format” tab – click the “Number” option right-arrow à select the required options accordingly from the list, such as “Number”, “Accounting”, “Financial”, “Currency”, “Currency rounded”, etc, as shown below.
Method #3 – Using the formulas.
We have certain formulas, such as TEXT(), CONCATENATE(), TEXTJOIN(), SUBSTITUTE() and also the Ampersand (&) to Insert Comma in Google Sheets.
- TEXT() – The syntax is “TEXT(number, format)”, where we provide the number argument value and then the format we want the final result in.
- CONCATENATE() – The syntax is “CONCATENATE(string1, [string2,…])”, where we provide the (string1, [string2,…]) argument values with the separator comma within double-quotes as “,” .
- TEXTJOIN()- The syntax is “TEXTJOIN(delimiter, ignore_empty, text1, [text2,…])”, where we provide the (text1, [text2,…]) argument values, with the separator comma within double-quotes as “,” for delimiter, and ignore_empty will be just the double-quotes as “”.
- SUBSTITUTE() – The syntax is “SUSBSTITUE(text_to_search, search_for, replace_with, [occurrence_number])”, where we provide the “text_to_search” argument value, “search_for” as the to be replaced value and “replace_with” as the new value.
- Ampersand (&) – The syntax is “value1 &“,”& value2”, where we provide the “value1, value2” argument value and the separator “,” in-between the two Ampersand (&) symbolsinserted that indicates that the values are joined with an comma.
Examples
Let us consider some Comma in Google Sheets examples to insert commas using the above-mentioned functions.
Example #1 – Add Commas using the TEXT Formula
Consider the dataset given below with a sale amount and we will Add Commas using the TEXT Formula to format the amount to currency.
The procedure to add commas using the TEXT function are as follows,
Select cell C2, enter the formula =TEXT(A2,”$###,###,###”) and press “Enter”, as shown below.
Example #2 – Add Commas using CONCATENATE Function.
The dataset given below consists of the first and the last names of actors of a famous sitcom. We will Add Commas using CONCATENATE Function to combine the names and display in the proper format.
The steps to Add Commas using CONCATENATE Function are,
Step 1: Select cell C2 and enter the formula =CONCATENATE(B2,”, “,A2), as shown below.
Step 2: Press “Enter” and drag the formula from cell C2 to C7 using the fill handle to get the following results.
Example #3 – Add Commas using ‘&’ Operator
Let us consider the same dataset of Example #2 and this time we will Add Commas using ‘&’ Operator to combine the names and display in the proper format.
The steps to Add Commas using ‘&’ Operator are,
Step 1: Select cell C2 and enter the formula =B2&”, ”&A2, as shown below.
Step 2: Press “Enter” and drag the formula from cell C2 to C7 using the fill handle to get the following results.
Example #4 – Add Commas using TEXTJOIN Function
The dataset consists of names for which we will Add Commas using TEXTJOIN Function.
The procedure to add commas using the TEXTJOIN function are as follows,
Select cell B2, enter the formula =TEXTJOIN(“, “,””,A2:A7) and press “Enter”, as shown below.
How To Replace Comma With Dot In Google Sheets?
Consider the dataset that consists Indian city names with their respective states with a comma at the end of each name. We will Replace Comma in Google Sheets with Dot using the SUBSTITUTE function.
The steps to Replace Comma with Dot in Google Sheets are,
Step 1: Select cell B2 and enter the formula =SUBSTITUTE(A2,”,”,”.”), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B5 using the fill handle to get the following results.
Important Things To Note
- The Comma in Google Sheets option will work for valid numbers, unprotected sheets, and the correctly updated Thousands separator option in the Google Sheets Options window.
- The Comma option inserts Commas only as a 1000s separator by default.
Frequently Asked Questions (FAQs)
A few reasons the Comma in Google Sheets may not work are,
1. The worksheet or workbook is password-protected.
2. The entered numeric data is invalid.
3. We have modified or deleted the dataset that has changed the format of the cells.
Another alternate way to add Comma in Google Sheets is the copy-paste special method, that can be performed as follows:
1. If an existing cell is already in the comma format, then, select the cell, press the shortcut keys to copy, i.e., “Ctrl+C” and go to a new cell where we want to highlight and press the shortcut keys to paste format only, i.e., “Ctrl+Alt+V”. Also,
2. If we do not want to use the shortcut keys, the path to the copy-paste special method is, “Edit – Copy” to copy the format and to “Edit – Paste special à Format only” to paste the format, as shown in the images below.
Yes, we can use the Paint Format tool to insert Comma in Google Sheets except if there is an existing cell that is comma formatted. Then, we can copy the format using the “Paint format” tool and paste it accordingly as required.
The shortcut keys to apply the default number format, which includes commas, to the selected cells are “Ctrl + Shift + 1“.
1. The advantages and disadvantages of using Comma in Google Sheets are,
a. The advantages of the Comma in Google Sheets are as follows:
It improves the readability of large numbers.
b. It helps highlight significant digits in a number, which is quite resourceful while working on financial reports.
c. It ensures to analyze the data for trends with enhanced accuracy.
2. The disadvantages of the Comma in Google Sheets are as follows:
a. It always inserts the Comma as a separator for 1000s i.e., international system. So, we must again change format for other currency values, manually.
b. When we use the Comma option or apply the Accounting number format in a cell or range, the resulting data in the chosen cell or range will appear right-aligned, then we must change the alignment accordingly.
We can insert the CONCATENATE in Google Sheets as follows:
Choose an empty cell for the output à select the “Insert” tab – click the “Function” option right arrow – click the “Text” option right arrow – select the “CONCATENATE” function, as shown below.
Since the TEXT, TEXTJOIN, SUBSTITUTE functions are all text function, we can use the same path to insert these functions, respectively.
Download Template
This article must be helpful to understand the Comma In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Comma In Google Sheets. We learn the various ways to add it to display different values with examples and working template. You can learn more from the following articles. –
Leave a Reply