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.
Read More: How to Count Specific Characters in a Cell in Excel
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.Â
Read More: How to Count Characters in Cell without Spaces in Excel
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
Download Practice Workbook
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!
