# Use the LEN function to get a cell's character count

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

• 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