Now that we’re familiar with LEFT, RIGHT, and MID, Excel Video 158 combines FIND and LEN into one formula that doesn’t require the extra columns for FIND and LEN that we’ve used in the past Excel Videos. When you first see the combined formulas, you’ll probably wonder why would I want to combine functions in the first place? The formulas look much more complicated. Can’t I just do a separate column for FIND and LEN like we’ve done in the past?
The answer is obviously yes, you can always break the formula into separate columns to make the components easier to understand. You can even hide the columns when you’re finished so your end users don’t have to see the extra columns.
As you get more comfortable with these text functions, there are some advantages to combining the functions into one formula. If somehow your additional columns for FIND and LEN get changed or deleted, the combined formulas will still work. If you want to copy the formulas to a new worksheet, it’s easier to copy and reference one formula than several formulas. If you’ve hidden the FIND and LEN columns and you don’t copy those hidden columns to the new spreadsheet, you’ll have to troubleshoot the new formulas to make them work.
Here are the three formulas from the video:
=LEFT(A4,FIND(“,”,A4)-1) pulls the last name
=RIGHT(A4,LEN(A4)-FIND(“,”,A4)-1) pulls the first name and middle initial
=MID(A4,FIND(“,”,A4)+2,LEN(A4)-FIND(“,”,A4)-3) pulls just the first name
