LEN Excel Formula

Author Zaheer    Category Formulas     Tags ,

You may ask if Microsoft Excel contains a feature that counts the words or characters in a cell. The answer is yes, but this feature is not visible in the worksheet. To obtain a character count in a particular cell, you need to use a one of the functions available in Microsoft Excel. The function is the so-called, LEN function. For you to learn about this function, read on this article.

Have you ever wonder of finding the character count feature in Microsoft Excel? Actually, that feature doesn’t exist in Excel, unlike the word count feature of Microsoft Word. MS Excel is not even a word processor. In Excel, it is not possible to count the character contained in a block of text by just selecting text block and see the character count. However, Excel contains an effective little function which is called the LEN function that allows you to count the characters by utilizing a formula.

The LEN function requires a text value as its only input, but instead of using the actual text value, you can also input the cell reference. In using the LEN function, you will just apply the formula for LEN function which is, =LEN(cell reference). For a better understanding, here’s an example.

As an example, “EXCEL” is contained in cell A1. Then enter this formula in cell B1, =LEN(A1). The function will return the character count in cell A1 which is 5. When character count in cell A1 is altered or either increases or decreases, the LEN function will recalculate the character count in the cell.

So, in general, the LEN function returns the number of characters contained in a cell. To learn more about this function, here are more examples and some exceptions.

  1. For normal texts

For example, you have the text “Sharmaine” in cell A2. As you will notice the text “Sharmaine” contains 9 characters, therefore, the LEN function will return the result which is 9. It’s very simple and nothing is complicated.

  1. For date formulas

For instance you use the date formula which is =TODAY(). As an example, the date “16-Feb-09” is contained in cell A3. The LEN function will return the result which is 5. But “16-Feb-09” contains 9 characters. You may wonder and say what’s happening. For you to know, for dates, Microsoft Excel stores them as numerical values. Therefore, the value of “16-Feb-09” is actually 39860, and the LEN function will return the result which is 5.

  1. NOW() formatted as a date

Take for instance that you use the sample date in no.2 which is “16-Feb-09”. If you use the NOW() function, the LEN will return the result which is 16. Again, you may wonder why is this so. In Excel, the NOW() is represented by a number. So what happens is this, 39860.4762944444, and apart from being formatted as a date, the function’s internal representation is still the same.

  1. For numerical values like this, P5,000.00

Excel’s interpretation of this example is that, it is a number. Then the LEN function will return the result which is 4, that is, the 4 digits for 5000.

  1. For numerical value like this, P588.00, which is pasted from the internet

For example you have pasted the value “P588.00” from an internet source. You might anticipate that the LEN function will return the result which is 3, but it’s actually not. Instead, the LEN function will return the result which is 8. The problem here is that, when you will copy and then paste the data from the Internet, it often acquires additional characters. These extra characters must be eliminated so that Excel would comprehend the numbers. In this example, the LEN function considers that “P588.00” is a string, and calculates all the characters even the one at the beginning. You have to take note that the Len function can be employed to quickly analyze data and see in case there are any trouble such as odd characters or additional spaces since the function will return a greater number that the one that you expect.

  1. For values like this, “=Sharmaine”

Take for instance that “=Sharmaine” is contained in a cell. If you use the LEN function to count the number of characters, the function will return the result which is 9, the 9 character for the value “Sharmaine”.

Post comment