Tag Archives: LEN

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.

An Automated Alternative in Excel

Automated alternative to FlashFill (and the “Text to Columns” Wizard)
My Excel tip is not so much about a favorite “tip or trick” as it is about a favorite concept. Tools like FlashFill and the “Text to Columns” Wizard are neat and certainly have their place, but I prefer an automated approach when appropriate. I emphasize “when appropriate” because one should always exercise good professional judgment when determining the correct tools and course of action to solve problems or complete tasks.
There are two reasons for my preference for automation when appropriate. First, an automated process usually requires less cumulative effort in repetitive process situations. Second, an automated process often can be shifted to lower-skilled workers, freeing those with higher skills to do other tasks that cannot be so delegated.
Let’s look at an example. In the spreadsheet below, each employee name in column B is composed of the employee’s last name and first name, separated by a comma delimiter and a space. If this is a “one-off” situation (particularly one having only a few names), you could (and perhaps should) use FlashFill or the “Text to Columns” Wizard to separate the names into their individual components.
Let’s say, however, that this data must be collected and separated each month for a large number of employees. Let’s also say that MS-Access (or a similar tool) is not available, and you do not want (or know how) to script code in VBA. Nesting functions can be an excellent way to automate tasks in Excel and create some interesting results.
For our example, Excel doesn’t have a “reverse concatenate” or “split” function, so we’ll have to get creative. In column C, we have extracted the last name from column B by nesting the SEARCH function inside the LEFT function. In column D, we have used the same approach, but substituted the RIGHT function for the LEFT function. Because the formula cell references are relative, they easily adjust as we copy them to the remaining cells. For reference, the two formulas are included in columns F and G.excel screenshot

Now let’s explain what just happened, using cell B3 for our explanation. In column C, we used the left function to extract the leftmost characters of cell B3 for the Last Name.

The second parameter of the LEFT function is used to specify the number of characters to return. Here, we have let Excel calculate the correct number of characters to return by using the SEARCH function to return the position of the comma in the cell contents — but we’re not done. We don’t want the comma returned, so we must adjust the calculated number of characters to exclude it.
Now we want to extract the First Name from cell B3. To do this, we use a combination of the RIGHT, SEARCH, and LEN functions.
Here, too, we allow Excel to provide the correct number of characters for the second parameter of the primary function. Conceptually, we will simply subtract the Last Name from cell B3 to arrive at the First Name.
However, we have a problem. We know the width of column B, but we don’t know the length of the content in each cell in column B without doing a lot of manual counting.
The answer? Let Excel figure it out, using the LEN function. The resulting formula tells Excel to subtract the leftmost characters from the cell content starting from (and excluding) the comma, and return the remaining (rightmost) characters. Once again, we exclude the comma from the results.
In actual fact, we should be asking Excel to exclude both the comma and the space (rather than the comma alone) from the results in both formulas. However, Excel is smart enough to trim the leading and trailing spaces from the results.
How can we set this monthly task up for delegation to an employee with lower skills? One way would be to house the employee names in one spreadsheet (or workbook) and house the formulas in another. Assuming data updates cannot be automated, the lower-skilled employee would simply copy or re-key the data into the specified position of the designated worksheet and the formulas would do the rest.

This tip is a guest blog post  from L.Keith Jordan, CPA
The LEN function in particular is totally underutilized. The LEN function in particular is very underutilized. If you want some other examples of what to do with it check out my Excel-Diva blog post on LEN.

 

  • “It is rare that i can actually say that I enjoyed a CPE course but I found this one informative and very interesting, relying more on the author’s examples and not just quoting tax code!  [Healthcare Reform: The affordable Care Act Tax Provisions]

    - Thomas

  • Your website was easy to navigate and informative.” “This was my first use of your resources but I’m sure it won’t be my last. Thank you for your excellent course material and succinctly stated instructions which expedited order and payment of course selection, completion of exam, and immediate availability of Certificate of Completion. A great experience. Thanks.

    - Draper

  • “I am extremely impressed with the courses and the reasonable price.” -Janet A. May 2017

    - Janet