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