Excel has a simple function called LEN for length that calculates the number of characters in a text string. LEN is a surprisingly useful function.
Let's take a look.
The LEN function takes just one argument, the text you want to count. If I supply the address B5, which contains the text "Susan" I'll get a result of 5, since there are 5 letters in the name.
The same formula will return the number eight for the text "New York". That's because LEN also counts space characters.
The abbreviation NY, returns two, as you'd expect.
Be aware that text might contain an extra trailing space which can throw off the count. For example, if I add a space at the end of NY, it isn't visible, but LEN returns 3.
We'll look at a way to solve this problem with a function called TRIM in another video.
What about numbers? Let me copy the formula down so you can see what we get. For B8, which contains $100.00, we get 3. This doesn't seem right, but if I check the contents of the cell, the raw number is actually 100, which is 3 digits.
With the number 1,000,000, which has a number format that uses commas as a thousands separator, LEN returns 7. This is because the there are 7 digits in the raw number.
For the date, June 1, 2016, we get 5. But if I click into the cell, we see a regular date, which looks like it would have a length of 8 characters. Why do we see 5?
The answer again is that Excel is counting the length of the actual number. Dates are stored as serial numbers in Excel which is why LEN returns 5.
This is easier to understand if I set the number format for these last 3 entries to General. Then we see only the actual numbers.
So remember that LEN ignores number formatting when counting characters.
Finally, I want to show you briefly how LEN can be used in other formulas. Each of these formulas uses LEN as a helper function to in a more complex calculation.
The first example counts the number of "e"s in the text. The second example counts total words, and the last example counts how many times "fox" appears in the text.
So, while the LEN function may seem simplistic, you'll often see it in other formulas that manipulate text.
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.