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 five since there are five 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 we can’t see it, but LEN returns three. 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 three. This doesn’t seem right, but if I check the contents of the cell, the raw number is actually 100 which is three digits. With the number 1,000,000, which has a number format that uses commas as a thousandths separator, LEN returns seven. This is because there are seven digits in the raw number. For the date June 1, 2016, we get five. But if I click into the cell, we see a regular date, which looks like it should have a length of eight characters. Why do we see five? The answer, again, is that Excel is counting the length of 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. 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 in more sophisticated ways.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
