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.


Description of the Excel LEN Function

The Excel LEN Function returns the number of characters in a string of text.


Excel LEN Function:

Syntax of the Excel LEN Function:

  • LEN(text)
  • 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.

Examples:

  • =LEN("LEN Me Your Ears") = 16
  • =LEN(A1) = The number of characters in cell A1
  • =LEN("abcdefghijklmnopqrstuvwxyz") = 26
Excel LEN Function

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.

Excel LEN Function - What?!

SAY
WHAT NOW?!


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:

The Excel LEN Function


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.

Conclusion

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​.
Joe Austin
 

Joe Austin is the founder of Potentials Unleashed. He is currently an IT Professional Manager in the Financial Industry and has been using computers since 1978. While mostly self-taught, he is proficient in several computing areas, such as Programming , Web Development, most Microsoft Office programs, Internet Marketing, Spyware / Malware Removal and even 3D Animation & Modeling.

Click Here to Leave a Comment Below 0 comments
%d bloggers like this: