How Long Is It? - LEN

If you need to know the number of characters in a cell, Excel can make the task of counting every letter, space, number, or other character MUCH simpler. But why would you need to know how many characters you have in a cell? I’m sure there are a plethora of reasons. For me, as a sales analyst, I occasionally need to work with zip codes.

Zip codes in the northeastern US all start with the number 0 (the zip code for Fenway Park in Boston is 02215, while one of the zip codes for Hartford CT is 06105) If the column that contains the zip codes is not formatted as text, then the zip codes that start with a zero have the leading zero removed. Excel thinks it’s a number (doesn’t know it’s a zip code) and therefore, 02215 as a number translates to 2215 which is what Excel displays. But if you try to mail a letter to zip code 2215, it will be returned faster than a Jonathan Papelbon high-fastball!

Therefore, in order to find which zip codes are in error (only 4 characters long as opposed to the regular zip codes that have 5 characters), the easiest way is to use the LEN formula. The LEN formula counts all characters including spaces within a cell.

As with many Excel formulas, the formula is simple once you know it exists! All you have to do is type =LEN(cell). That’s it! (You count the length of any piece of info, not just what's in a cell; just put the data you want to count in between the parenthesis.)

By using this formula in the zip code context I mentioned, you can easily see which zip codes are in error. By applying this to all zip codes on a worksheet, you can then sort them all and using the CONCATENATE formula you can correct them all quickly and easily.

And the result that you can easily copy and paste back to the original column that contains the zip codes:

Keep Excelling!

Do you like this post? Comment below and / or share on Facebook or Twitter!