How to Count Characters in Cell without Spaces in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

excel count characters in cell without spaces


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," ",""))

Count Character without Space using LEN

  • 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.

excel count characters in cell without spaces Using LEN combo

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," ","")))

Count Character without Space using sumproduc

  • After that, press the ENTER key.

Count Characters without space sumproduct

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.

Count Characters without space sum array

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.

excel count characters in cell without spaces using vba

  • After that, copy and paste the VBA code below.

VBA Code:

  • After that, press the F5 or play button to run the code.

Count Characters without space vba macro

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.

Count Character without Space


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


Related Articles

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.
Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo