Excel: Reuse Logic within a Formula

SIMPLE PROBLEM, COMPLEX SOLUTION

Consider, for example, a recent example I encountered. Someone needed to isolate the next-to-last word from each cell in a column. As I looked at their data, I noticed that the number of words in each cell varied. I also noticed that there were many places where someone had typed two spaces instead of one space. Solving the original problem will require addressing all these issues.

To simplify the problem, break the problem down into smaller steps, beginning with:

1. Eliminate multiple spaces. There’s an old trick for counting the number of words in a cell, but it requires a single space between each word. Thus, the first step toward solving the problem is to use =TRIM(A4) in cell B4. The TRIM function removes leading and trailing spaces and replaces all repeated spaces with a single space.

2. Count words in a cell. If you calculate the length of the phrase with LEN(B4) and then eliminate the spaces using SUBSTITUTE(B4,” “,””), the difference in length is one less than the number of words. Normally, I would use =LEN(B4)-LEN(SUBSTITUTE(B4,” “,””))+1 to get the number of words. To find the next-to-last word, however, subtract 1 instead of adding 1.

3. Replace one space with a marker. Find a character that will never appear in your text. I often use a carat (^), but a vertical pipe (|) will work as well. Use the SUBSTITUTE function to replace the Nth space in the phrase with the marker. Since C4 tells you which space to replace, the formula in D4 is =SUBSTITUTE(B4,“ ”,“^”,C4).

After these three steps, your task is easier: Find everything from the letter after the carat up to the space after the carat. A person having ordinary skill in the art of Excel should be able to follow the combination of MID, FIND, LEFT, and FIND to arrive at the answer shown in cell F4.

This video provides details on those formulas:

MERGING SEVERAL FORMULAS

This is a typical scenario: Five smaller steps will get you to the answer. But do you really want five columns of formulas?

At this point, I often try to merge everything into a single formula. The formula in cell F4 refers to the formula in E4 twice.

Select E4. Using the mouse in the Formula Bar, carefully select everything after the equals sign and then copy it.

Select F4. Click to select the characters “E4” in the formula.

Paste using Ctrl+V. This replaces the first “E4” with the formula from E4. While still in edit mode, select the other mention of “E4” (as shown below).

Paste to replace the second E4. You will now have a formula that refers to D4 a total of four times.

Copy the D4 formula without the equals sign from the Formula Bar and paste it four times into your formula. The formula keeps growing, and eventually you will use the B4 formula of TRIM(A4) a total of 12 times in the final formula:

=LEFT(MID(SUBSTITUTE(TRIM(A4)," ","^",LEN(TRIM(A4))-LEN(SUBSTITUTE(TRIM(A4)," ",""))-1),FIND("^",SUBSTITUTE(TRIM(A4)," ","^",LEN(TRIM(A4))-LEN(SUBSTITUTE(TRIM(A4)," ",""))-1))+1,30),FIND(" ",MID(SUBSTITUTE(TRIM(A4)," ","^",LEN(TRIM(A4))-LEN(SUBSTITUTE(TRIM(A4)," ",""))-1),FIND("^",SUBSTITUTE(TRIM(A4)," ","^",LEN(TRIM(A4))-LEN(SUBSTITUTE(TRIM(A4)," ",""))-1))+1,30))-1)

Explaining that formula to someone else will be difficult, if not impossible.

USE LET

Office 365 helps you simplify the formula using the new LET function. The equivalent formula using LET is:

=LET(

TrimText,TRIM(A4),

WhichSpace,LEN(TrimText)-LEN(SUBSTITUTE(TrimText," ",""))-1,

CaratText,SUBSTITUTE(TrimText," ","^",WhichSpace),

LastTwoWords,MID(CaratText,FIND("^",CaratText)+1,30),

LEFT(LastTwoWords,FIND(" ",LastTwoWords)-1)

)

You begin the LET function by defining a name and the formula that the name represents. I used “TrimText” to mean TRIM(B4). You don’t have to call it that; you could easily use “TT” or “T” instead. But TrimText will help others follow your logic.

After defining TrimText, you can define WhichSpace. Notice that the definition for WhichSpace reuses the name TrimText twice. On the next line, define CaratText. This definition reuses TrimText and WhichSpace. Then define LastTwoWords.

So far, each definition in LET requires two arguments: the variable name and the formula used to calculate that variable. The final argument in LET is the formula to define the answer that will appear in the cell. You don’t need a variable name here. The final argument of LEFT(LastTwoWords,FIND(“ ”,LastTwoWords)-1) will isolate the answer.

The LET function simplifies an entire class of problems where you need to repeatedly refer to an earlier component later in the formula.

SF SAYS

The LET function is available now to Office Insiders and will be rolling out to all Office 365 subscribers by the end of 2020.