Using FIND, LEFT, and RIGHT Functions to Extract Data in Excel


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.



Connecting Data Sets using VLOOKUP in Excel

In my dream world, the data management systems I use would allow me to extract data in the exact sequence and combinations I need. More often, I am able to extract only certain segments of data and then have to build bridges to connect those data sets outside of the original source.

Within the same or multiple workbooks, different data sets may be brought together using the VLOOKUP function. In this example, we reminisce to the lazy, hazy days of attending summer camp as a kid. Campers Becky, Joel, Vicky, Julia, and Anthony have signed up for various activities each day of the week. Their choices have been entered in the data management system as Activity Codes and are listed on the spreadsheet named Campers.


VLOOKUP_Excel_3In order to create a schedule for each camper for the week, the translation of the codes to the Activity name and Location will be accomplished using VLOOKUP. In a second sheet named Activities, the data is setup such that the Activity Code is in the first column of the data range. The Activity Code fulfills the first requirement in using VLOOKUP in that this is the common ID or key that exists in the each dataset. The second requirement is that the Lookup table is sorted by this ID or key.


To connect these two sets of data, the VLOOKUP formula will be entered in the Campers spreadsheet cell D2.



The first parameter needed to identify Becky’s Monday Activity is the Activity Code found in cell C2.

The next parameter is the address of the data containing the name and location of the date — the absolute reference Activities!$A$1:$D$6.

The next value is the column number of our target data, Activity in the 2nd column.

The last value in the VLOOKUP syntax will be FALSE. FALSE is used when an exact match is required. The alternative TRUE would be used if an approximate match was desired. I use FALSE almost 100% of the time. In fact, I’m not sure if I’ve ever wanted an approximate match and chose TRUE.

Using almost all of the same values, the Location may be filled by changing only the third parameter in the VLOOKUP formula. In cell E2 on the Campers sheet, we’ll type =VLOOKUP(C2, Activities!$A$1:$D$6,4,False). This will lookup on the same row of code AL001 in the Activities spreadsheet but will return the value in column 4 — Pool 1.


Copying the formulas to connect the data sets for all remaining rows will enable schedules to be prepared for all campers. Let the fun begin!


If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.


A Quicker Way to Move or Copy Spreadsheets in Excel

Moving or copying spreadsheets to another location is a straightforward process from the Cells group on the Home menu tab. A quicker way is to drag and drop to the new location.

Move or Copy using Cells Group

Let’s say we have a workbook which captures data for multiple years with one sheet providing summary information. The individual spreadsheets are named as follows:Move_Copy_Sheets_Excel_1 We would like to add data for 2013 and move Summary as the first spreadsheet. Select the Summary sheet. From the Home menu tab, click on the Format down arrow in the Cells group.


The dialog box launched will allow a different workbook to be chosen. In this case, we leave the default as the existing, “Revenue Forecasts.” We want to move Summary before the first sheet which is 2010. After selecting 2010, click OK. Because we are moving, not copying, the Create a Copy checkbox is unchecked.


Next, we’ll create a copy of the 2012 sheet and rename it 2013. Select Format from the Cells group and select Move or Copy Sheet. The book remains “Revenue Forecast,” we click on Sheet5, check the Create a copy checkbox, and click OK.


Clicking back on Format in the Cell group, this time, we select Rename Sheet. The 2012 (2) is highlighted and typing “2013” completes our task.


The Quicker Way — Move or Copy using Drag and Drop

(Too quick even for screenshots!)

1.  Click and hold the Summary tab, drag to the left of the 2010 tab and release.

2. Hold down the CTRL key, click and hold the 2012 tab, drag to the right one position and release.

3. Right click the 2012 (2) tab, click on Rename Sheet, type 2013, and press Enter.


Move or Copy Between Workbooks

Both methods work equally well across two open workbooks.  When using the Cell Group method, the Move or Copy dialog box will allow the second  workbook to be chosen and then the location and the move or copy choices may be made just like in a single workbook.


To drag and drop, only one extra step is needed for a second workbook. Once both workbooks are open, select View Side by Side in the View tab. If only two workbooks are open, they will appear side by side. If more than two workbooks are open, a dialog box will open in order to select the workbook to show next to the original.



Now that the two workbooks are side by side (or top and bottom), the original spreadsheet may be dragged to move to the other workbook or CTRL dragged to copy.

Shrinking a Spreadsheet to Fit on One Page in Excel

Photo: David Castor via Wikimedia Commons

Ever wish you could shrink your car when faced with parallel parking in a really tight space? That’s how I feel when a spreadsheet is just slightly bigger than will fit on one page. The usual solutions are choosing larger paper (e.g., 11″ x 17″), making the page margins smaller or shrinking the size of the columns or rows. That can be a lot of trial and error, making a change, checking Page Preview, tweaking a little more, checking again (not unlike inching the car forward, reverse, forward, reverse).

The faster solution is to use Scaling in the Print menu.

1. Click File on the Menu bar, select Print, and then Scaling under Settings. The default is No Scaling.


2. Click on the dialog launcher (small down arrow) and 3 other options appear.


Fit Sheet on One Page will take all of the selected print area or the whole sheet and shrink everything horizontally and vertically to fit on one page.

Fit All Columns on One Page will shrink the selection horizontally so that all columns fit on a page, but rows will print on as many pages as needed.

Fit All Rows on One Page will shrink the selection vertically so that all rows fit on one page but if the columns do not fit on one page, they will flow to additional pages.

The Print Preview should adjust as you choose different options.

How to Create Fill-in Forms using Excel

On a recent shopping trip to purchase a wedding present, I came to a standstill in the kitchen appliance aisle. I was stunned by the number of “one trick ponies” beckoning for my attention – waffle makers, Mickey Mouse shaped waffle makers, rice cookers, bread makers, silver dollar pancake makers, panini makers, sandwich grills. I wondered if it wouldn’t be more space and cost efficient to just use a good set of pots and pans.

So it is with software designed to do a singular task. I am sometimes infatuated by the latest marketing claims that promise to make quick order of a particular function. With a little more thought, however, I am usually swayed back to my tried and true tools. One example is making fill-in forms, several software packages may be purchased for this task. However, creating fill-in forms in Excel takes just a few steps.

There are two settings that must be coordinated, both found in the Format dropdown in the Cells group of the Ribbon. The default settings for the two settings are set as Protect Sheet set to OFF and Lock Cells set to ON. We’ll double check the Lock Cells as a first step.

1. Press CTRL-A to select the whole worksheet. Click on the drop-down arrow on Format in the Cells group. Check that the Lock Cells is ON, indicated by the shaded square around the lock icon. If in doubt, click the lock and the shaded background will toggle on and off.


2. Create the form with appropriate labels and open cells in which the users will input their information. For example, Cell A2 below shows the label “Name.” Cells B2 and C2 are blank and Merged as one cell for entry of the user’s name.


3. Select each of the input cells, in the above case, B2, C2, E2, and B3, C3, D3, E3. Cells which are non-contiguous may be selected by holding down the CTRL key while clicking on each cell.

4. Click on the drop-down arrow on Format in the Cells group. In the Protection section, click on Lock Cell. This will toggle OFF the lock for only the selected cells.


5. Click Protect Sheet right above the Lock Cell option. The dialog box provides several options to enable a customized user experience. Protecting the spreadsheet with a password will prevent the user from making any changes to the form without entering the password. Checking the box “Select unlocked cells” and unchecking “Select locked cells” will allow the user to conveniently tab through all of the input cells and skip over the label cells. Forms_Excel_6

6. Finally, save the file as an Excel Template so that each time the file is opened, a new copy is opened, leaving the original intact for future uses.

To see this form in action, download it here: Fill-in Form in Excel