Top of my to-do list this week were preparations for a special event planned for next month. Our invitees are pulled from our database using certain criteria. Though spouses may not meet the selection criteria, we do want to include spouses in the invitation. Our database generates a list as follows:
Invitations may be generated now with the data just like it is. Our word processing program will mail merge directly from this list in Excel. On the other hand, the name tags would look very goofy with both spouses’ names. In order to accurately create the name tags, we need to separate the first names into two columns, Name 1 in column C and Name 2 in column D. Fortunately, the word and is consistently used and we’ll use the FIND, LEFT, and RIGHT functions to find the names relative to "and."
Extracting Name 1 using LEFT() and FIND()
The LEFT() function has two parameters. The first is the source cell in which the data is located. The second is the number of characters that is to be returned, counting from the left side of the source. The most straightforward example might be a cell that contains a 7 digit part number (e.g., cell A1) with the first two characters representing the product category. The formula =LEFT(A1,2) would return the 2 digit product category.
Our need is slightly more complicated. Since different names have different lengths, we’ll use FIND() to calculate how many characters to return. The formula =FIND(" and ",B2) returns the answer of 9. That tells us that the first name is 8 characters long and the space before “and” is in position 9. We’ll nest the FIND() function within the LEFT() function and end up with
=LEFT(B2,FIND(" and ",B2)-1).
Copying the formula to all rows identifies another complication. In the Keiler household, there is only one person so our formula returns an error.
One solution is to use ISNUMBER to evaluate whether " and " exists in the cell and if TRUE, follow our above formula. If FALSE, the Name 1 result generated by the formula will be the same as the Salutation (column B). Putting it altogether:
=IF(ISNUMBER(FIND(" and ",B2)),LEFT(B2,FIND(" and ",B2)-1),B2)
Extracting Name 2 using FIND() and RIGHT()
The RIGHT() function is similar to LEFT(), returning a specified number of characters from the right side of the source data. Since FIND(" and ",B2) yields a result of 9, we can add 4 to calculate the position of the space at the end of "and." This means that Name 2 begins in the next position. Now we just need to calculate the total length of the cell’s contents to determine the length of Name 2. The LEN(B2) function gives us the total length as 17. Since we know from the FIND() portion of our formula that Name 1 plus " and " equals 13 characters, 17 minus 13 equals 4 which is the length of Name 2, Mary, in the Robertson family. It’s easy to see when we count it out as follows:
Nesting each function we have:
=RIGHT(B2,LEN(B2)-(FIND(" and ",B2)+4)) Copying it through all rows, here we go…
ARGH! There’s that error again! We forgot to test if the Salutation included and before embarking on the calculation. Fortunately, we can use the same logic of the ISNUMBER function to test that first. If there is no " and ", the cell should be left blank, otherwise the second name is extracted to Column D.
=IF(ISNUMBER(FIND(" and ",B4)),RIGHT(B4,LEN(B4)-(FIND(" and ",B4)+4)),””)
The names are now all separated and name tags can be prepared.
If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.