2 Easy Ways to Count Characters in Cell Without Spaces in Excel

To count characters in a cell without spaces in Excel, follow the steps below:

Select the blank cell.
Insert the formula: =LEN(SUBSTITUTE(C5,” “,””))
Press Enter.

As a result, the formula shows the number of characters without spaces in the given cell.

Counting characters without spaces in Excel is most useful for publishing an article, submitting an assignment, or designing cards. You can count how many characters are going onto a brochure, or business card to fit them into an object. Also, sometimes you need to clean the extra spaces (both leading and trailing) at the beginning or end of data while working with large data. They are hardly noticeable but you can avoid them by counting characters excluding all spaces.

In this Excel tutorial, you will learn 2 ways to count characters in a cell without any spaces using Excel functions and VBA. You will also count the characters excluding spaces in multiple cells.

See this article’s overview image where I have counted the Short Bio characters using LEN and SUBSTITUTE functions.

To count characters in a cell without spaces in Excel, here are 2 methods:

Combining LEN and SUBSTITUTE Functions

To count the number of characters within a cell, you can use a formula combining LEN and SUBSTITUTE functions. If your characters are in proper cases and you need to count characters excluding all the spaces, this is the best approach. This method also serves well if the characters have leading or trailing spaces i.e. any extra spaces. Suppose you have a Short Bio of an athlete in cell C5 and you want to count the number of letters without any spaces in cell E5.

To count characters without any spaces of a single cell using LEN and SUBSTITUTE functions, follow the below steps:

Select the cell you want to see the output.
Insert the formula: =LEN(SUBSTITUTE(C5,” “,””))
Press Enter.
Drag the Fill Handle to copy the formula to the rest of the cells.

As a result, the formula calculates the length of a cell’s content in cell C5 after removing all the spaces. The SUBSTITUTE function is used to replace spaces (” “) with nothing (“”), effectively removing them. Then, the LEN function calculates the length of the resulting string.

Note: If you mistakenly add any extra spaces (leading or trailing spaces), this formula can count characters excluding those extra spaces too.

Using Excel VBA

You can count the number of characters within a single or multiple cells through Excel VBA. This method is extremely efficient for larger data and you can count many cells with a single click.

To calculate the number of characters excluding all spaces using VBA, follow the below steps:

Click Developer tab > Visual Basic.
Or, press the Alt + F11 keys.
Microsoft Visual Basic for Applications window will appear.
Select the Insert option > Module.
The Module window will pop out.

Insert the following code in the Module window:

Sub CountCharactersExcludingSpaces()
'Developed by ExcelDemy
Dim i As Long
Dim cellValue As String
Dim charCount As Long
For i = 5 To 8
cellValue = ActiveSheet.Range("C" & i).Value
charCount = 0
Dim j As Long
For j = 1 To Len(cellValue)
If Mid(cellValue, j, 1) <> " " Then
charCount = charCount + 1
End If
Next j
ActiveSheet.Range("D" & i).Value = charCount
Next i
End Sub

Click the green Run button to execute the code.

After running the VBA code, the total character counts of the specified range pop up.

How to Count Characters in Multiple Cells Without Spaces in Excel

To calculate the number of characters excluding spaces in multiple cells or ranges, you can use Excel functions like SUM or SUMPRODUCT along with the LEN & SUBSTITUTE combined formula. Consider you have multiple lines of short bio in range C5:C8 and you want to count all the characters in that range excluding the spaces.

To count characters in multiple cells or ranges without spaces in Excel using the SUM, LEN & SUBSTITUTE functions, follow the steps:

Select a cell.
Use the formula: =SUM(LEN(SUBSTITUTE(C5:C8,” “,””)))
Or, apply this formula: =SUMPRODUCT(LEN(SUBSTITUTE(C5:C8,” “,””)))
Press Enter.

As a result, the formula calculates the total number of characters in a range of cells (C5 to C8) after removing all the spaces. It does this by using the SUBSTITUTE function to replace spaces with nothing and then finding the length (number of characters) for each cell using the LEN function. The SUM or SUMPRODUCT functions then add up these lengths, giving the overall count of characters in the specified cell range without including any spaces.

Download Practice Workbook

Count Characters Without Spaces.xlsm

Conclusion

This has been a guide to count characters in a cell without spaces in Excel. This tutorial also aimed to understand how to use different tactics for different data. Learning these methods can help you in designing cards and cleaning data. Please leave your queries or suggestions in the comments area below. Thank you for your read.

Frequently Asked Questions

Here are some frequently asked questions and answers regarding this topic.

How Can I Count All Characters in One or Multiple Cells Including Spaces?

Use the LEN function to count all the characters in one or multiple cells including spaces. Here are 2 cases:

For single cell: =LEN(A1)

For multiple cells: =SUM(LEN(A1:A10))

How Do I Count Specific Text in Excel?

Use this COUNTIF formula to count texts in Excel: =COUNTIF(C5:C8, “player”)

How Can I Count Characters in a Cell Excluding Extra Spaces Only?

Combine LEN and TRIM functions to count characters excluding the extra spaces.

Use this LEN and TRIM combined formula: =LEN(TRIM(C5))

<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!