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.
Download Practice Workbook
4 Ways to Count Characters in Cell without Spaces in Excel
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.
- Type the following formula in Cell D5.
- 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.
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.
- First Type the following formula in Cell D6.
- 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 total that numbers and give 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.
- First, type the following formula in cell D6.
- And, press the CTRL+SHIFT+ENTER key.
Here, the formula will operate as an ARRAY formula if we press CTRL+SHIFT+ENTER altogether.
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.
- First, right-click on the sheet and go to View Code.
- After that, copy and paste the VBA code below.
- After that, press the F5 or play button to run the code.
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.
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