Ways to Count Characters in Cell Without Spaces in Excel: 2 Methods

Combining LEN and SUBSTITUTE Functions

You can use a formula combining LEN and SUBSTITUTE functions to count the number of characters within a cell. This is the best approach if your characters are in proper cases, and you must count characters, excluding all the spaces. 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 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.

After removing all the spaces, the formula calculates the length of a cell’s content in cell C5. The SUBSTITUTE function is used to replace spaces (” “) with nothing (“”), effectively removing them. 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.

Method 2 – Using Excel VBA

You can count the number of characters within a single or multiple cells through Excel VBA. 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

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

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.

After removing all the spaces, the formula calculates the total number of characters in a range of cells (C5 to C8). It uses the SUBSTITUTE function to replace spaces with nothing and then finds each cell’s length (number of characters) using the LEN function. The SUM or SUMPRODUCT functions add up these lengths, giving the overall characters count in the specified cell range without including any spaces.

Download Practice Workbook

Count Characters Without Spaces.xlsm