LEN Function

LEN Function – Counting Characters in a Cell

 

LEN() is one of those functions that you wonder about. LEN is a text function and depending upon what you are doing it is so useful. It counts the number of characters in a cell.  I use it all the time if I want to drop a minus sign at the end of a number of  if I imported values that have turned into text because the numbers have a CR after them.

In this example, I had a column of stores and they all began with Contoso. Since it was a bit redundant I wanted to get rid of the word Contoso at the beginning of each store name.

LEN function

Now, using =RIGHT(A2,18) works for the first and second store as they are 18 characters in length when Contoso is excluded but the Kennewick Store only needs 16 characters instead of 18 so the formula doesn’t work for that store or some of the others on the list that are of different lengths.
An easy way around this is to combine RIGHT with LEN.

=LEN(A2) = 26 counts  26 characters in cell A2.
Every store name has the word Contoso  in it and Contoso has 7 characters and that is what we want to eliminate.

So, =RIGHT(A2,LEN(A2)-7) is the answer.
When you break it down the formula ends up being =RIGHT(26-7) for the first store. 26 characters less the first 7 characters. Same for the second store. Now the store name in A3 has fewer characters so LEN counts 23 instead of 26 and ends up 23-7 = 16 characters  so it would display Kennewick Store. As you copy it down, LEN will count the number of characters that comprise the various store names and then the formula will subtract the first 7 characters.  Pretty slick and definitely useful.

Leave a Reply

Your email address will not be published. Required fields are marked *

  • “The course on ethics was one of the best correspondence courses I ever used.  It was well organized and the examples used were extremely helpful.”

    - Tommy R.

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom

  • “Thank you for a high quality CPE resource. As a CPA in a small nonprofit, I find your library quite helpful to meet my needs. I appreciate the service.”- 

    - Cheryl