You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Ever wonder where the character count command is in Excel? It doesn't exist, at least not in the same sense as Word's word count feature--Excel isn't a word processor. You can't select a block of text and quickly see how many characters the selection contains. But Excel does have a nifty little function, LEN, that lets you count characters by using a formula.
LEN takes a text string as its only input - though of course you can use a cell reference instead of actual text data. To use LEN, just enter the formula as you would any other. Here's an example:
You can see the formula in the formula bar (the selected cell is D9). When the text in C9 changes, the LEN function recalculates.
What about word count? That's a little trickier--but you can read more about counting in the Excel Help article Ways to count values in a worksheet, which includes a section on word count.
-- Steven Thomas
Comments: (4) Collapse
You can also find how many characters minus the spaces by nesting the SUBSTITUTE() function...
=LEN(SUBSTITUTE(A1," ",""))
Replace A1 with whatever cell you're looking to check character length in. Sometimes this may be useful because a lot of people don't want to include spaces - which are characters - when counting alpha-numerics. Also, in addition to the link above to count the number of words, here is a non-array entered formula (link shows an array formula) you can use. It's based on the same logic: the total number of words is the total number of spaces plus one...
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
Again replacing A1 with whatever cell you're looking at to check word count. This can be problemmatic if either you have leading or trailing spaces, or use a double space between sentences. To have a more complete, all-inclusive formula for those particular problems, you can add to the formula a little bit...
=LEN(TRIM(A1))-LEN(TRIM(SUBSTITUTE(A1," ","")))+1
The TRIM() function will take care of leading/trailing spaces, while the SUBSTITUTE() function will take care of double (or more) spaces.
Zack Barresse
Great tip, Zack! Thank you. :)
... and, just to round out the field, here's a VBA function that will count the number of words in a string:
'----------
Public Function WordCount(ByVal WordString As String) As Long
'Return the number of "words" in the passed string.
Dim arrWords As Variant
'Condense all multiple spaces down to a single space
Do While InStr(WordString, Space$(2))
WordString = Replace(WordString, Space$(2), Space$(1))
Loop
'Create an array by breaking up the string at each space character.
arrWords = Split(WordString, Space$(1))
'The upper boundary of the array (+1 because it's zero-based) will contain
' the number of words in the string. Return this number to the calling routine.
If IsArray(arrWords) Then
WordCount = UBound(arrWords) + 1
Else
WordCount = 0
End If
End Function
-Mark
Way to raise the bar, Mark! Thanks for the code tip.
-Steven
Comments: (loading) Collapse