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:

1. Select the cell you want to see the output.
2. Insert the formula: =LEN(SUBSTITUTE(C5,” “,””))
3. Press Enter.
4. 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:

1. Click Developer tab > Visual Basic.
   Or press the Alt + F11 keys.
   Microsoft Visual Basic for Applications window will appear.
2. Select the Insert option > Module.
   The Module window will pop out.
3. 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

4. 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:

1. Select a cell.
2. Use the formula: =SUM(LEN(SUBSTITUTE(C5:C8,” “,””)))
   Or, apply this formula: =SUMPRODUCT(LEN(SUBSTITUTE(C5:C8,” “,””)))
3. 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

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

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
• How to Count Occurrences of Character in String in Excel
• How to Count Specific Characters in a Cell in Excel

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