Friday, February 24, 2012
How To Use Excel LEN Function
Posted on 2:42 AM by Unknown
LEN is one of the core text functions in Excel that all users should have in their set of skills. The function will return the length of a specified text string. You might wonder when you will use this?. But, once you start to do any sort of data manipulation in Excel you will soon realise how useful it really is.
So how does it work?. LEN will return EVERY character in a text string. So, all text, spaces, punctuation and numbers will all be included in the count of characters in question.
Let's look at an example or two. The syntax for the formula is
=LEN(Text)
See an example text string below:-
Excel is awesome.
Applying the above formula the result would be 17
Here is another example:-
howtoexcelatexcel
Applying the above formula the result would be 17
So, just how will this be useful? If you do any data work with Excel, in particular with data importing or data cleansing this function can be invaluable. Imagine you have a set of records to import from one database system to another. The new system you know will not accept a name field longer than certain character length.
You know the fields that have restrictions in the new database, but your old system records did not have such controls. With this information you could easily use Excel LEN function as a method of identifying the name records that were too long in length.
You can insert a additional column to calculate the length of the text string. The records can be sorted using this extra columns that contains the results of the LEN function. Easy.
Here are the detailed steps to carrying out such an exercise
- Open your Excel file
- Insert a column to the right of the field you wish to measure
- Enter a column title in this example 'name length'
- Place the Excel cursor in the first cell underneath your column title
- Formulas Tab
- Function Library
- In the search for a function box type LEN
- Hit Ok
- Click the cell that contains the text string you want to perform the character count
- Hit Ok
The result of the count of the text string will be displayed, all you need to do is copy the formula to your other cells in the column.
You can quickly do this by selecting the first cell in your column with the formula, scroll down the column to the last cell in the column you want to analyse.
- Hit SHIFT-Click in the last cell to select them all.
- Press CTRL+D to fill with your formula all of the way down the column.
You now have the count of the string of characters in your selected name records and can sort them to identify those which are over the threshold of what is allowed in the new database system.
Excel is often thought of as a number cruncher and that's its limit. LEN is one example of Excels built in functions that can be used to help you work smarter and faster to solve real problems with data.
Subscribe to:
Post Comments (Atom)
Feedage
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
No Response to "How To Use Excel LEN Function"
Leave A Reply