Asif Khan Pranto
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 Len Function Combining 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.
Download Practice Workbook
5 Ways to Count Characters in Cell Including Spaces in Excel
1. Count Characters in Cell Including Spaces Using LEN Function
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 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: Count the Number of Specific Characters in a Cell in Excel (2 Approaches)
2. Using LEN Function for Range of Cell(s) to Count Characters Including Spaces
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 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 Number of Characters in a Cell in Excel (Easiest 6 Ways)
3. Count Characters Including Spaces of Range of Cell Using SUM & LEN Functions
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.
Read More: How to Count Characters in Cell up to Space in Excel
4. Using Excel SUMPRODUCT & LEN Functions to Count Characters in Cell Including Spaces for Range of Cells
Here I will show you in Excel Count Characters in Cell Including Spaces using the SUMPRODUCT & LEN Function 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. Count Characters Having in between Spaces Excluding Leading & Trailing Spaces
A scenario may appear where we want to exclude Leading & Trailing Spaces but want to Include the Space in between. If that’s the case, TRIM Function can rescue us. Using LEN & TRIM Function together we can exclude Leading & 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 actually too high even if we Count in between Spaces. It is because there were Leading & 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 used 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 & Trailing 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.
Practice Sheet
Here I have provided a Practice sheet for you. You can easily experiment here with the above methods.
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!
