Do you ever need to count the number of characters in Google Sheets?
Let’s start with a simple example.
Let’s say you have a cell that contains the text “Hello, world!”.
How many characters does that text contain?
Can you guess?
The answer is 13.
But what if we want to count the number of characters without spaces?
Can you guess that answer too?
The answer is 12.
Whether you want to count the number of characters, including spaces, or the number of characters without spaces, I’ll show you how to do it in this blog post.
Let’s get started!
Also read: (opens in new tab)
Google Sheets Date Picker
Group Sheets in Google Sheets
Can You Highlight Text in Google Sheets
Smooth Scrolling Google Sheets
Character Count in Google Sheets
To count characters in Google Sheets, you can use the LEN function. This function counts all characters, including spaces. If you want to count characters without spaces, you can combine the LEN function with SUBSTITUTE.
Character Count in Google Sheets With Spaces
Step 1: Click the cell where you want to count the characters with spaces
The first step is to open the spreadsheet and select a cell where you want to display the character count.
For instance, if your text is in cell A1, select the cell next to it (B1).
Click inside this cell and proceed to the next step.
Step 2: Type the formula: =LEN(A1)
Now, in the cell, copy and paste this formula: =LEN(A1)
Make sure to change the cell number in the formula to match the cell you want to check.
For instance, if you want to see the number of characters in cell B1 and your text is in cell A1, enter the formula “=LEN(A1)” into cell B1.
Step 3: Press Enter
After entering the formula, press the enter key to run it and view the results.
The cell will display the number of characters in the text, including spaces.
Step 4: Drag the cell corner to apply the formula to the remaining cells
If you have a column full of text and you want to find the number of characters in each cell, manually entering a formula for every cell can be time-consuming.
However, you don’t have to do this.
Simply drag the cell corner to the bottom to apply the formula to all the other cells in the column.
Character Count in Google Sheets Without Spaces
Step 1: Click the cell where you want to count the characters without spaces
The method described above counts the characters, including spaces.
However, if you want to count the characters without spaces, that method won’t work.
You’ll need a different formula, and we’ll explain the steps in this section.
To start, select a cell where you want to see the number of characters (excluding spaces) and click inside it.
Step 2: Type the formula: =LEN(SUBSTITUTE(A1, ” “, “”))
Copy and paste this formula into the cell: =LEN(SUBSTITUTE(A1, " ", ""))
Since my text is in cell A1, I have entered “A1” in the formula.
If your text is in another cell, you can make changes accordingly.
Step 3: Press Enter
After entering the formula and making changes to the cell number, take a quick glance to ensure that you have entered the correct cell number.
If everything is correct, press the Enter key to view the results.
Step 4: Drag the cell corner to apply the formula to the remaining cells
To apply the formula to the remaining cells, simply drag the corner of the cell.
This shortcut saves time, as you do not need to enter the formula and change the cell number for every cell.
Instead, dragging the first cell will apply the formula to the rest of the cells.
If a cell has no text, the result will be “0”.
FAQs
How do I count characters in Google?
To count characters in Google Sheets, use the LEN function. It counts all characters, including spaces. Type “=LEN(A1)” in a cell to count the characters in cell A1. To exclude spaces, use “=LEN(SUBSTITUTE(A1, ” “, “”))”.
What is the Len function in Google Sheets?
The LEN function in Google Sheets counts the number of characters in a cell, including spaces.
Character count Google Sheets formula
In Google Sheets, you can count characters in a cell using two formulas. To count characters, including spaces, use =LEN(A1) replacing “A1” with the cell containing the text you want to analyze. For character count excluding spaces, utilize =LEN(SUBSTITUTE(A1, " ", "")).
Summary
Counting characters in Google Sheets is a simple process that can be achieved using the LEN function or a combination of the LEN function with SUBSTITUTE.
Follow these steps to count the characters in Google Sheets:
- Counting Characters with Spaces: Use “=LEN(A1)” in the target cell, replacing “A1” with your text cell. Press Enter to see the character count, including spaces.
- Counting Characters without Spaces: Enter “=LEN(SUBSTITUTE(A1, ” “, “”))” in the desired cell, substituting “A1” with your text cell. Press Enter to view the character count, excluding spaces.