# How to Count Characters in Cell Including Spaces in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn in Excel to count characters in cell including spaces. MS Excel has a built-in LENÂ function to count characters including spaces & we can easily execute the LEN function combined with several other functions too.

Here, we have a dataset of five English movies in column B. We want to count characters of cell B5:B9 including spaces. Iâ€™ll show you how to do it using several methods.

## 1. Using Excel LEN Function to Count Characters in Cell Including Spaces

Here we want to count characters of the Movie Name Column in the Count Column. Using the LEN function for Single or Multiple Cells will make our task easier.

Step 1:Â

• First, we have to select a Cell where we want to see the count. Iâ€™ve selected Cell E5.
• Then we have to type the LEN Function here in order to count the number of characters of â€˜Green Mileâ€™ including space.
• The formula is
`=LEN(B5)`

Step 2:Â

• Upon pressing the ENTER key we will see the count of characters of â€˜Green Mileâ€™ in Cell E5 including space.
• The name â€˜Green Mileâ€™ has 9 characters but there is a space in between. So counting it Len Function will Show the Count to be 10.

Step 3:Â

• Now we can drag the Fill Handle to AutoFill the Count Column & the Len Function will be executed automatically for each Cell.

• If we look attentively at the image below we will realize that in Cell B7 I have put two extra spaces after the Name & in Cell B8 there are two spaces between the word â€˜Catchâ€™ & â€˜Meâ€™. Len Function will Count all of those spaces. It has also Counted Colon (:) of Cell B9.

## 2. Counting Characters Including Spaces with LEN Function for Range of Cell(s)

Here I am going to show how to count the sum of the Range of Cells including in between spaces using the LEN function.

Steps:

• First, we have to select a Cell where we want to see the characters count including spaces of Cells Ranging from B5:B9. Here I have selected Cell C10.
• In Cell C10 we have to type the Formula to sum up the characters count of Cells Range B5:B9 which will Include Spaces.
• The Formula is
`=LEN(B5)+LEN(B6)+LEN(B7)+LEN(B8)+LEN(B9)`

• Now upon pressing the ENTER key, we will see the total characters count of Range B5:B9 Including Spaces in Cell C10.Â

## 3. Joining SUM & LEN Functions to Count Cell Characters Including Spaces

Here we will learn using the SUM Function & LEN function together to count the characters of the Range of Cells having spaces.

Steps:

• Initially, we have to select a Cell where we want to see the characters count including spacesÂ of Cells Ranging from B5:B9 using the SUM & LEN Functions.Â  Here I have selected Cell C10.
• Then we have to Combine SUM & LEN functions to write the Formula in Cell C10 for Range B5:B9.
• The formula is
`=SUM(LEN(B5:B9))`

Here, using the LEN function we counted the characters of the cell range B5:B9 then used the SUM function to get the total of the counted characters.

• Now upon pressing the ENTER key we will see total characters count of Range B5:B9 including spacesÂ in Cell C10 which has used both SUM & LEN Formula.

## 4. Using SUMPRODUCT & LEN to Count Characters Including Spaces for Range of Cells

Here I will show you in Excel Count Characters in Cell Including Spaces using the SUMPRODUCT & LEN functions together.

Steps:

• First, I have selected Cell C7 where we want to see the Characters Count Including Spaces of Cells Ranging from B5:B9 using SUMPRODUCT & LEN functions.
• Then we have to Combine SUMPRODUCT & LEN functions to write the Formula in Cell C7 for Range B5:B9.
• The formula is
`=SUMPRODUCT(LEN(B5:B9))`

Here, using the LEN function we counted the number of characters of the cell range B5:B9 then used the SUMPRODUCT function to get the total of the counted characters.

• Now upon pressing the ENTER key, we will see the total characters count of Range B5:B9Â including spacesÂ in Cell C7 which has used both SUMPRODUCT & LEN functions.

## 5. Counting Characters Excluding Leading & Trailing Spaces in Excel

A scenario may appear where we want to exclude leading and trailing spaces but want to Include the Space in between. If thatâ€™s the case, TRIM Function can rescue us. Using LEN & TRIM functions together we can exclude leading and trailing spaces while keeping in between Spaces. Letâ€™s see how it works!

Here in Column C or Count Before Trim Column, we have counted the characters from Column B. But looking carefully we will find out that the Count is too high even if we count in betweenÂ paces. It is because there were leading and trailing spacesÂ before every word & LEN Function has counted that space too. TRIM Function can solve this problem.

Steps:

• First, we have to select a Cell. Here I have selected Cell D5 from the After Trim Column where I want to Trim Cell B5 data.
• Type the following formula into the formula bar or in cell D5.
`=LEN(TRIM(B5))`

Now we have used the TRIM function to remove the leading and trailing spaces of Cell B5. Then the LEN function was to count the number of characters excluding leading and trailing spaces for Cell B5.

• Upon pressing the Enter key it Counts the Characters of Cell B5 excluding leading and trailing spacesÂ gaps where in between Spaces will be included.

• Now we can drag the Fill Handle to AutoFill the Count After Trim Column & the Formula used in Cell D5 will be executed Automatically for each Cell of Count After Trim Column.

Read More: How to Count Space Before Text in Excel

## Conclusion

Reading the following article you have learned in Excel to count characters in cell including spaces. I hope this article will help you. If you have any queries regarding Excel please leave a comment. Take care!

## What is ExcelDemy?

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

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. Iâ€™ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF