Priti
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.
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,” “,””))
🔨 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.
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,” “,””)))
🔨 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.
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,” “,””)))
🔨 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.
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.
📝 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.
