How to Count Characters in Cell up to Space in Excel

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.

Excel Count Characters in Cell up to Space


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.

Excel Count Characters in Cell up to Space

  • 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.

Excel Count Characters in Cell up to Space


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)


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.

Count All Characters in a Range of Cells in Excel

  • Alternatively, you can use the following formula to get the same result.
=SUMPRODUCT(LEN(B5:B10))


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


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.


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.

Count Characters in Cells with VBA in Excel

  • 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.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo