How to count characters with the LEN function

Let’s take a look.

TheLEN functiontakes just one argument: the text you want to count.

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.

We’ll look at a way to solve this problem with a function calledTRIMin another video.

Let me copy the formula down, so you’re able to see what we get.

For B8, which contains $100.00, we get three.

This is because there are seven digits in the raw number.

For the date June 1, 2016, we get five.

Why do we see five?

The answer, again, is that Excel is counting thelengthof the actual number.

In Excel, dates are stored as serial numbers, which is why LEN returns five.

This is all easier to understand if I set the number format for the last three entries to General.

Then we see only actual numbers.

So, when you use the LEN function, remember that it ignores number formatting.

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 in a more complex calculation.

The first example counts the number of “e’s” in the text.