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

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

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

### 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

## 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 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

Advanced Excel Exercises with Solutions PDF