Yes Friday again more Formula Fun time. Today it’s exploring the LEN function. It really is one of the core text formulas to have in your Excel Toolbox. It is used to return the length of a specified text string i.e it counts the number of characters in the piece of text. So, do you find yourself importing data and finding inconsistencies? LEN is a great Excel function to quickly highlight where your issues are. It is an easy way to make sure your data is clean before using it in calculations, charts, etc. Learn how to use this powerful function today!
LEN Function Syntax Recap.
Syntax of the formula is very straughtforward.
=LEN(text) The text for which to calculate length.
So how could this be useful?. One example would be highlighting data issues such as data fields longer than expected. These undoubtedly would cause errors on a data import from one system to another. So it would be hugely beneficial to highlight any issues before the import takes place. Use the Excel LEN function to spot any text that is too long. Let’s add the extra effort with conditional formatting to highlight the data fields too long for import.
Here is a sample output from my old database. The address line is not restricted and allows 255 characters.
However, my new database restricts the address field to 35 characters. I see trouble ahead.
Let’s go ahead and use the Excel LEN function to help identify any potential issues.
- Open your Excel file. This is my old Excel database.
- Insert an extra column to allow is to populate it with the results of the LEN formula. I have inserted a column to right of Column C.
- Label the column.
- Select the Formula tab, then the Insert Function. Alternatively select the first cell in the column and hit equals to begin the formula.
- Select LEN or type LEN the open brackets (
- Click on the cell you want to determine the length of string (in this example it is B2).
- Using the formula wizard, Excel will display the answer immediately, close brackets and hit Ok to finish typing the formula manually.
All we need to to do is copy the formula down the whole column
- Scroll down with your mouse and SHIFT-click in the last cell in your column
- Press Ctrl+D
Add Conditional Formatting
We now know the character county using the Excel Len Function. Let’s add that oomph and highlight character count cells with conditional formatting.
- Select Column C cells
- Home Tab- Style Group
- Conditional Formatting
- Highlight Cell Rules
- Select Greater Than-. This highlights for any cells that are greater than 30 characters. So type 30 and select the type of formatting- I will leave as the default suggested by Excel.
- Hit Ok
There we go, all the cells that will cause issued are highlighted. We have two problem records in rows 2 and 9. We can now data tidy up before the data is imported into the new database.
A few points to note on the Excel LEN function.
The LEN function will count and return the total number of characters for:
- text strings
- number data
- special characters – such as hyphens, ampersands, and percent signs
- non – printing characters
- space characters – including the spaces between words
If you want more Excel tips like the LEN function then don’t forget to sign up on the top right to my Excel newsletter- 3 Excel tips every 15 days- free. You get a free EBook of my favorite 50 Excel tips.
