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.
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.