3 Ways to Count Characters in Cell Including Spaces in Excel

Get FREE Advanced Excel Exercises with Solutions!

To count characters in a cell including spaces follow the steps below:

  1. Select any cell to show the count.
  2. Insert the formula in the cell: =LEN(desired_cell)
  3. Press Enter.

For example, to count the characters in cell A1 including spaces, the formula will be: =LEN(A1)


Counting characters in a cell or a range, including spaces, can be useful for various purposes such as limiting the length of a text entry, checking the consistency of a data format, or extracting a substring based on a character position.

In this Excel tutorial, you will learn how to count characters in Excel in a cell or a range using different functions and formulas in Excel. Additionally, you will learn how to count characters excluding leading and trailing spaces.

Here I have some movie names. I’ve counted the number of characters in those cells in the following image.

excel count characters in cell including spaces


How to Count Characters in a Cell Including Spaces in Excel?

To count characters in a cell, we will use the LEN function. The LEN function calculates the number of characters in a text string, including spaces, punctuation, and numbers.

To count characters in a cell including spaces using the LEN function, follow the steps below:

  1. Select any cell to show the count.
  2. Insert the formula in the cell:
    =LEN(B5)
    Replace B5 with the desired cell containing characters.
  3. Press Enter.
    This will give us the character count of the text in the specified cell including spaces.
  4. Drag the Fill Handle to apply the formula for the rest of the cells (if any).

The cells with the character count will appear accordingly.

Count Characters in Cell Including Spaces in Excel

Read More: How to Count Specific Characters in a Cell in Excel

How to Count Characters in a Range Including Spaces in Excel?

While you can use only one function to count the number of characters in a cell, counting the number of characters in a range of cells requires formulas combining multiple functions. There are multiple ways to to count characters in a cell including spaces.

Here are the 2 ways to count characters in a range including spaces:

Using the LEN Function

The LEN function can count the number of characters of one cell at a time. For multiple cells, you can simply add the individual LEN function for each cell in the range and add them manually. This method is handy for a range with a small number of cells.

The formula is: =LEN(cell1)+LEN(cell2)+LEN(cell3)+…
You can add as much number of cells as you need.

To count characters in a range including spaces using the LEN function, follow the steps below:

  1. Select any cell to see the count.
  2. Insert the formula in the cell:
    =LEN(B5)+LEN(B6)+LEN(B7)+LEN(B8)+LEN(B9)
    Replace B5, B6, B7, B8, and B9 with the desired cells. Add or remove the LEN function if needed.
  3. Press Enter.

The output of the character count will appear in the cell.

Count Characters in Cell Including Spaces in Excel

Read More: How to Count Characters in Cell without Spaces in Excel

Using the Combination of SUM and LEN Function

Using the LEN function repeatedly can be time-consuming and inefficient for larger data ranges. Instead, you can use the SUM function. The LEN function will count the characters including spaces while the SUM function will add the number of all characters in that range.

To count characters in a range including spaces using the SUM and the LEN function, follow the steps below:

  1. Select any cell to see the count.
  2. Insert the formula in the cell:
    =SUM(LEN(B5:B9))
    Replace B5:B9 with the desired cell range.
  3. Press Enter.

The output of the character count will appear in the cell.

Count Characters in Cell Including Spaces in Excel

Alternatively, you can use the SUMPRODUCT function instead of the SUM function. This will give the same result for a specified cell range. To do so, follow the steps below:

  1. Select any cell to see the count.
  2. Insert the formula in the cell:
    =SUMPRODUCT(LEN(B5:B9))
    Replace B5:B9 with the desired cell range.
  3. Press Enter.

The output of the character count will appear in the cell.

Count Characters in Cell Including Spaces in Excel

How to Count Characters Excluding Leading & Trailing Spaces?

A scenario may appear where you want to exclude unnecessary leading and trailing spaces. If that’s the case, you can use the TRIM function. Using LEN and TRIM functions together we can exclude leading and trailing spaces while keeping in between Spaces.

In this example, we have counted the characters of the movie names in the Count Before Trim column. But the count of characters is too high there are leading and trailing spaces before every word and the LEN function has counted that space too. Now, we will count without the leading and trailing spaces.

Count Characters in Cell Including Spaces in Excel

To count characters excluding leading & trailing spaces, follow the steps below:

  1. Select any cell to show the count.
  2. Insert the formula in the cell:
    =LEN(TRIM(B5))
    Replace B5 with the desired cell containing text.
  3. Press Enter.
    This will give us the character count of the text in the specified cell without leading and trailing spaces.
  4. Drag the Fill Handle to apply the formula for the rest of the cells (if any).

The cells with the character count without leading & trailing spaces will appear accordingly.

=LEN(TRIM(B5))

Read More: How to Count Space Before Text in Excel


Download Practice Workbook

Conclusion

Counting characters including spaces is necessary for various purposes in Excel. in this article, we have shown different ways to count characters in Excel including spaces. We have also shown methods to count characters excluding leading and trailing unnecessary spaces keeping the spaces in between. Feel free to leave any comments if you have any questions regarding this topic. Thank you!

Frequently Asked Questions

How Do You Count Instances of a Character in a Cell in Excel?

You can use the SUBSTITUTE and the LEN function. For example, if the data is in cell A1 and the desired character is x, then use the formula in any cell: =LEN(A1) – LEN(SUBSTITUTE(A1, “x”, “”))

Pressing Enter will give you the number of instances of x in the A1 cell.

How Do I Count the Number of Spaces in a Cell in Excel?

To count the number of spaces in a cell, write this formula in any cell and press Enter: =LEN(A1) – LEN(SUBSTITUTE(A1, ” “, “”))

Replace A1 with your data cell.

What is the Maximum Number of Characters I Can Show in an Excel Cell?

You can show only 1024 characters in a cell while it can contain a maximum of 32767 characters. The Formula Bar can show all the 32767 characters at the same time.


Related Articles


<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asif Khan Pranto
Asif Khan Pranto

Md. Asif Khan Pranto worked as an Excel and VBA Content Developer in Exceldemy for over two years and published some articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical Engineering from the Islamic University of Technology. Now, he is pursuing a Master of Development Studies to experience a new spectrum of knowledge. Apart from creating Excel tutorials, he is interested in Data Analysis... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo