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

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.

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

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

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

Sub Count_number_of_characters_in_a_cell_excluding_spaces()

Dim Mysheet As Worksheet

Set Mysheet = Worksheets("VBA")

Mysheet.Range("D5") = Len(Application.WorksheetFunction.Substitute(Mysheet.Range("C5"), " ", ""))
Mysheet.Range("D6") = Len(Application.WorksheetFunction.Substitute(Mysheet.Range("C6"), " ", ""))
Mysheet.Range("D7") = Len(Application.WorksheetFunction.Substitute(Mysheet.Range("C7"), " ", ""))
Mysheet.Range("D8") = Len(Application.WorksheetFunction.Substitute(Mysheet.Range("C8"), " ", ""))

End Sub

• 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

Related Articles

• How to Count Specific Characters in a Column in Excel
• How to Count Space Before Text in Excel
• How to Count Alphabet in Excel Sheet