We need to count words while publishing an article, submitting an assignment, to see how many characters are going onto a business card, etc. We are going to learn the ways for Excel to count characters in cell without spaces. For your better understanding, we are going to use a sample dataset containing Name and Short Bio. We will see how many characters without space is in the Short Bio of each Name.
How to Count Characters in Cell without Spaces in Excel: 4 Ways
We will use the LEN function with different combinations of SUBSTITUTE, SUM, SUMPRODUCT, and VBA to count characters in cells without spaces.
Method 1: Count Characters in Cell without Spaces Using LEN Function
In our first method, we will see the combination of LEN and SUBSTITUTE functions to count characters without spaces.
Steps:
- Type the following formula in Cell D5.
=LEN(SUBSTITUTE(C5," ",""))
- Now, press the ENTER key.
Here, the SUBSTITUTE function will remove all the spaces and keep only the characters.
=SUBSTITUTE(C5," ","")
yields the result PrincipalowneroftheCharlotteHornetsoftheNBA
And the LEN function counts the number of characters.
- Finally, drag down using the mouse right key to AutoFill rest of the series.
Read More: How to Count Characters in Cell Including Spaces in Excel
Method 2: SUMPRODUCT and LEN Functions to Count Characters in Cell without Spaces
Earlier we have seen counting characters for a single cell. In this method, we will count characters in the range C5:C8. Thereās an easy way to do it.
Steps:
- First Type the following formula in Cell D6.
=SUMPRODUCT(LEN(SUBSTITUTE(C5:C8," ","")))
- After that, press the ENTER key.
Thatās it. LEN along with the SUBSTITUTE function counts the number of characters in each cell from C5 to C8 and the SUMPRODUCT totals those numbers and gives us the ultimate result.
Method 3: Count Characters in Cell without Spaces Using SUM and LEN Functions
We can also use a combination of SUM, LEN, and SUBSTITUTE functions to count characters in a specific range.
Steps:
- First, type the following formula in cell D6.
=SUM(LEN(SUBSTITUTE(C5:C8," ","")))
- And, press the CTRL+SHIFT+ENTER key.
Here, the formula will operate as an ARRAY formula if we press CTRL+SHIFT+ENTER altogether.
Read More: How to Count Specific Characters in a Cell in Excel
Method 4: Count Characters in Cell without Spaces by VBA
In this last section of the article, we will see a VBA code to count characters without spaces in Excel.
Steps:
- First, right-click on the sheet and go to View Code.
- After that, copy and paste the VBA code below.
VBA Code:
- After that, press the F5 or play button to run the code.
Done.
Read More: Excel VBA: Count Characters in Cell
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, weāve attached a practice workbook where you may practice these methods.
Download Practice Workbook
Conclusion
These are 4 different methods in Excel to Count Characters in Cell without Spaces. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback