Excel LEN Function: LEN Me Your Ears!
The Excel LEN Function is another Text manipulation function that will tell you how many characters are in a string of text. It is yet another one of the most used functions that is great to know, when you need to find out how long a string of text is.
Excel LEN Function:
Syntax of the Excel LEN Function:
- text (Required) - The text whose length you want to find. Like other text functions, spaces count as characters too.
- This function does NOT take into affect any text formatting, such as with Dates and Time values. Excel handles these as actual numbers. See image below.
- =LEN("LEN Me Your Ears") = 16
- =LEN(A1) = The number of characters in cell A1
- =LEN("abcdefghijklmnopqrstuvwxyz") = 26
Lucy! You Have Some 'Splainin' To Do!
The Excel LEN Function is really a very simple function, but there are a few cases where it doesn't seem to work as you might expect. The function does not take into affect any text formatting, such as Excel uses for Dates and Times. Excel handles these as actual numbers that make no sense to us, at first glance. For example, 10/31/2016 is represented in Excel as the number 42674 and 1:33:00 PM is represented as 0.564583333333333.
Here is Microsoft's explanation of how Dates and Times are represented:
In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.
Where did they come up with THAT format?
I think the above description, from Microsoft, is pretty clear when it comes to Time, but it leaves the Date format a little unclear. Basically, if 01/01/1900 equals 1, then 01/02/1900 equals 2 and so on. Today is 10/31/2016 (Happy Halloween!), so today's number is 42674. Today has been exactly 42674 days, since 01/01/1900. So it is really just a sequential number that started at the turn of the last Century.
Download The Example Spreadsheet Here:
Why use the Excel LEN Function?
If you do a lot of text manipulating of your data, especially for reporting, I probably don't even need to answer this. I have found it to be very handy to know how long a string of text is, so that I can parse it out into a more favorable format. This function, combined with other Text Functions, becomes a very powerful tool that gives you critical information about your data.
Finding the length of a given string of data can be extremely useful. This function, very simply, does just that. Once you have the length, you can use it to calculate many aspects of your data. This is a simple function that just helps to get the job done!
Share, Comment, Ask a Question or Rip Me a New One!
If you liked this article, please feel free to share it on social media and comment below if you have any suggestions, questions or corrections. Thank you for reading!
P.S. - Remember to sign up to the newsletter email list, so you can catch all of the new posts and learn about upcoming topics, products and/or services.