4 Handy Ways to Count Characters in Cell Without Spaces in Excel

You can count characters within a cell without any spaces using Excel. For example, you need to count the characters within a string or paragraph to make your message or post more appealing to many people. To do so, we need to count the characters in the cells without spaces. So, we’ll show you four easy methods to count the characters within a specific cell without any spaces using Excel.


πŸ“ Download Excel File

Download the Excel file used for the demonstration from the link below.


Learn to Count Characters in Cell Without Spaces in Excel with These 4 Methods

Let’s familiarize ourselves with the dataset we used throughout the article as an example. Here are some Shakespeare quotations in the first column. Now we want to count the characters in the cell that do not contain spaces. As a result, we will use this dataset to demonstrate four simple methods for counting characters in Excel cells without spaces.


Approach 1

1. Using LEN and SUBSTITUTE Functions

This first method will use the LEN and the SUBSTITUTE functions for counting characters in cells without spaces. SUBSTITUTE function replaces old text with new text in a text string. On the other hand, the character count in a text string is returned by LEN function. Let us go through the steps now.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want the desired result. Here, we are selecting cell C6 and insert the formula.

=LEN(SUBSTITUTE(B6,” β€œ,””))

USing LEN & SUBSTITUTE functions to count characters in cells without spaces in Excel

πŸ”¨ Formula Breakdown

Here, the formula we inserted in cell C6 is:

LEN(SUBSTITUTE(B6,” β€œ,””))

πŸ‘‰ Here, cell B6 contains the text string, and SUBSTITUTE function replaces all spaces with no space.

πŸ‘‰ Then, LEN function counts the number of all characters in the text string and returns the total number of the characters without space.

  • Then, select cell C6 again and click & drag the Fill Handle down, or Double-click on the plus that appeared to Autofill the cells after selecting. Finally, the outcome looks like this.


Approach 2

2. Employing LEN and SUMPRODUCT Functions

We will employ LEN and SUMPRODUCT functions in this second method to count all the characters in cells without spaces. The LEN function returns the number of the characters witin a text string, and SUMPRODUCT function adds the value returned from the LEN function. Here we will have the output as the sum of all cell’s characters without spaces. So, let us see the steps to apply these functions.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell C6 and insert the formula. We will have our results finally.

=SUMPRODUCT(LEN(SUBSTITUTE(B6:B10,” β€œ,””)))

Applying LEN & SUMPRODUCT to count characters in cells without spaces in Excel

πŸ”¨ Formula Breakdown

Here, the formula we inserted in cell C6 is:

SUMPRODUCT(LEN(SUBSTITUTE(B6:B10,” β€œ,””)))

πŸ‘‰ Here, cell B6:B10 contains the text strings, and SUBSTITUTE function replaces all spaces in between the characters with no space.

πŸ‘‰ Then, LEN function returns number of all characters in the text strings in the range and returns the total number of the characters in the string without space.

πŸ‘‰ Lastly, SUMPRODUCT function adds up all the values returned from the previous functions.


Approach 3

3. Applying LEN and SUM Functions

We will employ LEN andΒ SUM functions in this third method to count characters without spaces in cells. The LEN function returns the number of total characters within a text string, and SUM function adds the values returned from the LEN function. Here we will have the output as the sum of all cell’s characters without spaces. So, let us see the steps to apply these functions.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select cell C6 and insert the formula.

=SUM(LEN(SUBSTITUTE(B6:B10,” β€œ,””)))

Employing LEN & SUM functions to count characters in cells without spaces in Excel

πŸ”¨ Formula Breakdown

Here, the formula we inserted in cell C6 is:

SUM(LEN(SUBSTITUTE(B6:B10,” β€œ,””)))

πŸ‘‰ Here, the range B6:B10 contains the text strings, and the SUBSTITUTE function replaces all spaces between the characters with no space.

πŸ‘‰ Then, LEN function counts the characters in the text strings in the range and returns the total number of the characters in the string without space.

πŸ‘‰ Lastly, SUM function adds up all the values returned from the previous functions.


Approach 4

4. Embedding VBA

This method will demonstrate how to extract text from a cell in Excel using Microsoft Visual Basic Application (VBA). This VBA code counts characters within a specific cell without any spaces.

Also, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, go to the Developer tab and select Visual Basic from the code group.

  • Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

  • Enter the code and press F5.
Sub Counting_characters_in_cell_without_spaces()

Dim Currentsheet As Worksheet
Set Currentsheet = Worksheets("VBA")

Currentsheet.Range("C6") = Len(Application.WorksheetFunction.Substitute(Currentsheet.Range("B6"), " ", ""))
Currentsheet.Range("C7") = Len(Application.WorksheetFunction.Substitute(Currentsheet.Range("B7"), " ", ""))
Currentsheet.Range("C8") = Len(Application.WorksheetFunction.Substitute(Currentsheet.Range("B8"), " ", ""))
Currentsheet.Range("C9") = Len(Application.WorksheetFunction.Substitute(Currentsheet.Range("B9"), " ", ""))
Currentsheet.Range("C10") = Len(Application.WorksheetFunction.Substitute(Currentsheet.Range("B10"), " ", ""))

End Sub
  • Now press Alt+F8, select Counting_characters_in_cell_without_spaces, and then Run.

  • Lastly, we will have our outcome.

Embedding VBA to count characters in cells without spaces in Excel


πŸ“ Takeaways from This Article

πŸ“ŒΒ  This article demonstrated 4 methods to count all the characters in cells without spaces.

πŸ“ŒΒ  In the first method, we showed LEN and SUBSTITUTE functions for counting characters without spaces using Excel.

πŸ“ŒΒ  Then, we demonstrated how to use LEN and SUM functions to count the characters in cells without spaces.

πŸ“ŒΒ  After that, we showed LEN and SUMPRODUCT functions to count all the characters in cells without spaces.

πŸ“ŒΒ  Finally, we employed VBA code to count all the characters in cells without spaces using Excel.


Conclusion

This article has demonstrated 4 methods to count all the characters within a certain cell without any spaces using Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

(Visited 35 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo