How to count the number of characters in a cell in Excel

By SpreadCheaters
Page last updated: 07/12/2022 | Next review date: 07/12/2024
close

Author bio


SpreadCheaters
View More north_east

Excel is a very powerful tool to conduct multiple calculations with numeric data. However, it provides a variety of tools and functions to handle and manipulate text data as well. When you are working with text data, sometimes you need to know the total number of characters in a single cell or multiple cells. In such a situation Excel has built-in functions that can be used to count the numbers of characters of text data in a cell or multiple cells as well.

Look at the dataset above, it has text data in column A. Some of the text data has leading or trailing spaces with it and some data is clean. In some cases, we wish to calculate the number of characters including the spaces and sometimes we only want to know how many characters are actually there in the data. So, we’ll discuss both scenarios in this tutorial. We’ll use the LEN(text) function to count the number of characters in a cell.

Method 1 Count the number of characters with all spaces

Although our dataset has the same text i.e., John Doe in most of the cells but the leading, trailing and the spaces in between are variable. So, we’ll get different results for each cell due to this fact. Let’s find out how many characters each cell has by using the LEN function.

Syntax of LEN Function

The syntax of this function is very simple and is given below;

LEN(text)

text

This could be a text string with double quotes or a reference to the cell containing the text string. This function will count all the characters including all spaces i.e., leading, trailing and spaces in between.

Step 1 – Count the number of characters by using LEN function

  • Write the following formula in cell B2 to count the number of total characters including all the spaces in the cell,

=LEN(A2)

Method 2 Count the number of characters without all spaces

Although our dataset has the same text i.e., John Doe in most of the cells but the leading, trailing and the spaces in between are variable. So, we’ll get different results for each cell due to this fact. If we want to count only the number of actual characters without any spaces then we’ll have to use the SUBSTITUTE function to remove the spaces first. When we’ll count the number of characters after removing the spaces then we’ll see that we’ll get the same result each time John Doe appears in a cell. Let’s find out how to use SUBSTITUTE to remove the spaces from any text.

Syntax of SUBSTITUTE Function

The syntax of this function is as follows;

SUBSTITUTE(text, old_text, new_text, [instance_num])

The SUBSTITUTE function has the following arguments:

  • Text    

The text or the reference to a cell containing text for which you want to substitute characters.

  • Old_text   

The text you want to replace.

  • new_text    

The text you want to replace old_text with.

  • instance_num    

This is an optional parameter. It specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Step 1 – Remove all spaces in the cell with SUBSTITUTE

  • We’ll use the following formula to remove all the spaces in the cell.

=SUBSTITUTE(A2, “ ”, “”)

  • This will remove all spaces from A2 and then we’ll double click on the fill handle to copy the formula down the data range as shown above.

Step 2 – Count the number of characters using LEN function

  • Write the following formula in cell D2 to count the number of total characters including all the spaces in the cell,

=LEN(C2)