Category Archives: Tips & Tricks

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

FIND_LEFT_RIGHT_Excel_6

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:

FIND_LEFT_RIGHT_Excel_1

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.

FIND_LEFT_RIGHT_Excel_2

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)

FIND_LEFT_RIGHT_Excel_3

Perfect!

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:

FIND_LEFT_RIGHT_Excel_4

Nesting each function we have:

=RIGHT(B2,LEN(B2)-(FIND(" and ",B2)+4))   Copying it through all rows, here we go…

FIND_LEFT_RIGHT_Excel_5

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.

FIND_LEFT_RIGHT_Excel_7

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

Cheers,
Annie

 

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.

Move_Copy_Sheets_Excel_2

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.

Move_Copy_Sheets_Excel_3

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.

Move_Copy_Sheets_Excel_4

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.

Move_Copy_Sheets_Excel_5

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.

DONE!

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.

Move_Copy_Sheets_Excel_6

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.

Move_Copy_Sheets_Excel_7

Move_Copy_Sheets_Excel_8

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.

How to Sort Dates by Month and Day in Excel

Sort_Dates_Excel_3The new chairperson of your “Staff Party Planning Team” bounces in your office Monday morning. You learn that plans are underway to begin celebrating employees’ birthdays and the team needs a list of all staff. No problem. A quick download from the payroll system yields an alphabetical list of names and birthdays:

Sort_Dates_Excel_1

A quick re-sort to put in chronological and you’ll be done:

Sort_Dates_Excel_2

Well, not so fast. The chronological sort uses the full date and thus, places the list in order from oldest employee to youngest! What the team is looking for is to sort dates based on month and day only, such that all January birthdays would be listed first, then February, and so on.

Simple formulas will accomplish the goal. The functions MONTH(date) and DAY(date) will extract the month and day numbers to columns D  and E.

Sort_Dates_Excel_4

Sort_Dates_Excel_5

At last we are ready to sort! Select rows 1 through 8 and click on the down arrow on the Sort & Filter option in the Editing group on the Ribbon. In the drop down, click on Custom Sort.

Sort_Dates_Excel_7Because we included Row 1 in the selection, the box “My data has headers” should be checked. Had we selected Rows 2 through 8, Row 2 contains data rather than headers, so we would uncheck that box.

Including the header row makes selecting the sort levels very straightforward. Here we select Month as the first level and Day as the second level. Then click OK.

Sort_Dates_Excel_7

The sort is complete and now your Party Planning Team can be ready for each month’s celebrations. Let the good times roll!

Sort_Dates_Excel_8

 

How to Change Default Settings in Excel

“Any customer may have a car painted any color that he wants so long as it is black.”                Henry Ford

Modern day product development has come a long way from Henry Ford’s 1909 perspective about customer’s desires! Our lament is often the polar opposite, we now have so many product variations,  our problem is often how to choose between so many choices!

Though many options exist in Excel, new worksheets are created in Excel with default settings which may not suit your company’s style guidelines, the typical spreadsheets you create or even your own personal preferences. If you find yourself making repetitive changes in the spreadsheets you create, why not change the default settings? Here’s how:

1. Identify formatting you would like to set as your default. Examples are font, font size and color, column width, page size, margins, alignment, etc.

2. Search for the location of the XLSTART folder in Programs file hierarchy. Using Windows Explorer, search for XLSTART kind:=folder. Make note of the location. In my Win7 OS and Office installation, my XLSTART folder is c:\Program Files (x86)\Microsoft Office\Office14\XLSTART. Yours may be different.

3. Create a new blank workbook. Make all of your preferred changes.

4. Click File, Save As. Change_Default_1

5. Enter Book as the File Name. Select Excel Template in the Save as Type drop down options. Navigate to the XLSTART folder located in step 2. Click the Save button. The next time that Excel is started and a new workbook is created, it will match your changed settings.

Change_Default_2a

With all due respect Mr. Ford, I think I’ll take my font in PURPLE!

 

Keyboard Shortcuts in Excel for Formatting

Ever have one of those late nights finishing a marathon number crunching session? Just about the time you breathe that deep sigh of relief that everything is ticked and tied, you realize that your 9 am presentation tomorrow morning is only a few short hours away and your spreadsheet is nowhere near presentation ready.

Keyboard_Shortcuts_1
By MC3 Adam D. Wainwright [Public domain], via Wikimedia Commons
Having a few essential formatting shortcuts under your belt will make quick work of polishing your spreadsheet for printing or projecting. This is the third in a series of keyboard shortcuts that reduce the time reaching for the mouse to find and click common actions. Keyboard shortcuts for navigation may be found here and shortcuts for data entry and editing may be found here.

Hey, here’s one more tip before you turn out the lights for the night: Center that spreadsheet before printing. Now…..good night, sleep tight.

 

ACTIONSHORTCUT
BoldCTRL + B
ItalicsCTRL + I
UnderlineCTRL + U
Format BoxCTRL + 1
Outline borderSHIFT + CTRL + &
Remove borderSHIFT + CTRL + _
Add CommentSHIFT + F2
General Number CTRL + SHIFT + ~
Currency FormatCTRL + SHIFT + $
Percentage FormatCTRL + SHIFT + %
Exponential Number FormatCTRL + SHIFT + ^
Date formatCTRL + SHIFT + #
Time FormatCTRL + SHIFT + !