Md. Shamim Reza
This article illustrates how to count characters in a cell up to space in excel. Other examples of counting characters in cells in excel are also highlighted. You will also be able to learn how to use VBA in excel to count characters in cells. The following picture highlights the idea of this article.
Download Practice Workbook
You can download the practice workbook from the download button below.
How to Count Characters in Cell up to Space in Excel
Imagine you have a dataset containing a few full names. You want to get the count of characters of the first names only. That means you want to count characters in a cell up to space only.
- Then enter the following formula in cell C5. Then use the fill handle icon to apply it to the cells below as shown in the following picture.
=FIND(" ",B5,1)-1- The FIND function in this formula returns the position of a certain character (” “) in the cell.
- Alternatively, you can use the following function instead to get the same result.
=LEN(LEFT(B5,FIND(" ",B5,1)-1))- The LEFT function in this formula returns a number of characters from a text string. And the LEN function returns the number of characters in a text string.
More Examples to Count Characters in Excel
1. Count All Characters in a Cell in Excel
The LEN function in excel allows us to count characters in a cell.
- Enter the following formula in cell C5. This will return the number of total characters present in cell B5.
=LEN(B5)
Read More: Count Numbers in a Cell in Excel (3 Methods)
2. Count All Characters in a Range of Cells in Excel
Imagine you want to count the total number of characters in range B5:B10.
- Then enter the following formula in cell D5. Use CTRL+SHIFT+Enter to apply the formula if you are not using Office365.
=SUM(LEN(B5:B10))- The SUM function in this formula adds up all the cell lengths.
- Alternatively, you can use the following formula to get the same result.
=SUMPRODUCT(LEN(B5:B10))- Here the SUMPRODUCT function works the same as the SUM function.
3. Count Specific Characters in Cells in Excel
Assume you want to know how many times the letter “s” appears in those names.
- Then apply the following formula in cell C5. After that, use the fill handle icon.
=LEN(B5)-LEN(SUBSTITUTE(B5,"s",""))- The SUBSTITUTE function in this formula replaces the ‘s’ letters with empty.
Read More: Excel Count Specific Characters in Cell (4 Quick Ways)
4. Count Characters Excluding Special Characters in Excel
Suppose you want to count all characters in the cells excluding spaces.
- Then enter the following formula in cell C5.
=LEN(SUBSTITUTE(B5," ",""))- Notice carefully that one of the double quotes in the formula has space in between.
Read More: How to Count Characters in Cell Including Spaces in Excel (5 Methods)
5. Count Characters in Cells with VBA in Excel
You can also count characters in cells using Excel VBA. Follow the steps below to be able to do that.
📌 Steps
- First, press ALT+F11 to open the Microsoft Visual Basic for Applications window. Then select Insert >> Module to open a new blank module.
- After that, copy the following code.
Sub CountCharacters()
Sheets("AllCharVBA").Select
Dim i As Integer
Dim length As String
For i = 5 To 10
length = Len(Cells(i, 2))
Cells(i, 3).Value = length
Next i
End Sub- Next, paste the copied code on the blank module. Then press F5 to run the code.
- Finally, you will see the same result as obtained using the formula.
Things to Remember
- Press CTRL+SHIFT+Enter to enter array formulas.
- You can change the characters inside double quotes (“ ”) as needed.
- Change the worksheet name in the VBA code according to your excel sheet.
Conclusion
Now you know how to count characters in excel up to space. Please use the comment section below for further queries or suggestions. You can visit our blog to read more on excel. Stay with us and keep learning.
