Advanced String Manipulation Using MS Excel - The LEN function in MS Excel
(Page 4 of 4 )
The LEN function is probably the simplest of all text-related functions. Its job is to simply count the number of characters inside the cell, even SPACES! So be careful; do not rely on the LEN function to count only characters. I highly recommend using the LEN function mixed with other string-cleaning functions. This is to ensure that your string manipulations are correct.
For example if you have something like:
Beautiful women
placed in Cell A1, it comprises 15 characters. But if there are spaces before or after any words, your result with LEN will be more than 15 characters.
To clean the text to be analyzed, it is recommended to use this set of functions:
=trim(clean(A1))
The job of this function is to remove any spaces as well as non-printable characters. Finally the LEN function (including the cleaning formulas) will be:
=LEN(trim(clean(A1)))
To use this one in the actual Excel spreadsheet, we will use our first example in this article:
Instead of using:
=MID(A1,5,1000)
=MID(A2,5,1000)
=MID(A3,5,1000)
You can have:
=MID(A1,5,LEN(TRIM(CLEAN(A1))))
=MID(A2,5,LEN(TRIM(CLEAN(A2))))
=MID(A3,5,LEN(TRIM(CLEAN(A3))))
This can accurately count the number of characters, instead of using 1000 as the default value, and still produce the same results. Note that the LEN function is very versatile; it can be used along with FIND, MID and other text-related functions, as shown in the examples in this article.
Summary
We have successfully illustrated how to use the MID, FIND and LEN functions. These are very important Excel functions for conducting advanced text manipulations. You can start varying the example above and incorporate it into your daily work. As soon as you start acquiring these skills, they will help you to tremendously improve your office and analytical productivity using MS Excel.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 4 
