I like to think of myself as an advanced computer user and Excel is one of the programs that I feel moderately comfortable using. Some of the more advanced features of Excel can be confusing and, like anything, the best way to learn how to use it is to spend time interacting with the software. If you're not big on time, you might find the following Excel recipes to be worth following until you start to really "get" Excel:
To extract the last word in a string of text, place the following in cells to the right of the text. This is helpful for extracting last names from a column of names =RIGHT(B75,LEN(B75)-FIND("*",SUBSTITUTE(B75," ","*",LEN(B75)-LEN(SUBSTITUTE(B75," ",""))))) First Name Isolation: Or how to remove everything from a cell after the first space: =LEFT(A1,Find(" ",A1)-1)
Remove Duplicates
Be aware that using this may destroy your data. Work from a copy, not the original data, when attempting this:
Copy
your range of data to a blank section of the worksheet
Select
a cell in your data set.
From
the Data ribbon, choose Remove Duplicates.
The
Remove Duplicates dialog will give you a list of columns. Choose the
columns which should be considered.
Deactivate several hyperlinks at once To the uninformed, this one makes about as much sense as mixing non-dairy creamer with fire but the results are just as impressive:
Type the number 1 in a blank
cell, and right-click the cell.
Click Copy on the shortcut
menu.
While pressing CTRL, select
each hyperlink you want to deactivate.
Click Paste Special on the Edit
menu.
Under Operation, click Multiply
and then click OK.
Highlight Every Other Row Quickly
Use the "Conditional Formatting" option
To use the Conditional Formatting option to shade rows in a worksheet, follow these steps:
Open the worksheet.
Select the cell range that you want to shade, or press Ctrl+A to select the whole worksheet.
Click the Home tab.
In the Styles group, click Conditional Formatting, and then click Manage Rules.
Click New rule.
Click Use a formula to determine which cells to format.
In the Format values where this formula is true box, type =MOD(ROW(),2)=1, and then click Format.
On the Fill tab, click the color that you want to use to shade every other row, and then click OK.
Click OK to close the New Formatting Rule dialog box.
Click Apply, and then click OK to close the Conditional Formatting Rules Manager dialog box.
Combine the Contents of Two Cells
Useful in creating one cell with a person's full name when you have their first name in one cell and their last name in another
=CONCATENATE(A2," ",B2)
The above formula will combine the contents of two cells, in this case those in cell A2 and B2 and add a space between the cells
Splitting Cells Where There are Commas
When you have "Last Name, First Name" in a single cell, you might try moving them into different cells by using the following method to split the data split into multiple cells:
Data - Text to Columns
Get Rid of Data Between Parentheses
To remove data between parentheses from cells:
Just use the "Find/Replace" tool found on the "Edit Menu"...
1. Ctrl / Cmd + F or Select Find / Replace in the Edit menu
I visited the Jet Propulsion Laboratory (JPL) in Pasadena, CA and watched the Curiosity rover being built. Here are some photos of the clean room in which the craft was being assembled:
[I haven't found them yet.]
At 8:30 p.m. PDT, JPL promises to begin streaming live coverage of the landing. At approximately 10:30 p.m. PDT, Curiosity is due to touch down on the surface of Mars. Here is the site that promises to stream live coverage of tonight's landing.
Here and here are links to the official NASA pages describing the vehicle.
Here is an article from the New York Times on the upcoming Mars landing.
Who Doesn't Like a Game About Mindless Mass Suicide?
In the latest installment of my Better Enjoy Your Weekend series comes Lemmings. Lemmings was a game that was originally released on something that we called a 3.5" floppy disc despite the fact that it was rigid and only its predecessor the 5.25" disk was actually floppy. I actually remember playing this game from the DOS command prompt on my first computer, a 486 my family bought in the 1990s. I'm happy to report that through the magic of the internet the exact Lemmings game I remember from my childhood is available here.
Level 1
I suggest that if you do try to play the game, you do so on at least the "Tricky" difficulty setting because the "Fun" setting is too easy even for a guy like me who does not play a lot of video games. Also, if you do play, be prepared for the annoying fact that after you assign a task to a lemming you will have to click on the action icon twice to select it for the next Lemming.
Protip: After washing your clothing several times it will become thinner and, possibly, even see through. The stuff that collects in the
lint trap of your dryer is material that came off your clothing.
Ladies (and guys too, I shudder to
think) if you have spandex outer wear that you have been wearing for
years, it might be time to think about tossing it out. Last Saturday morning, as I was
sitting in Starbucks reading the news and writing, a group of women
walked in after what could only be the end of a nearby exercise class. One woman was wearing a matching purple
tank top and shorts both made from spandex that left the white triangle of her thong very obviously visible through both the shorts and the bottom of
the tank top, which was pulled down over her shorts. I wonder what the people facing the front of her
outfit saw.
Getting Thrown Out of Congress
Thad McCotter joins Pluto on the list of things who have been kicked out of the cool kids club.
Technically, Thad McCotter retired but it's fairly clear that had he not given up his seat in the U.S. House of Representatives, he would have been thrown out of Congress because he did not actually meet the requirements to hold the office. The New York Times reports that "1,563 of the 1,830 signatures he turned in to get his name on the primary ballot were found to be fraudulent. Only 1,000 were required." The full New York Times story is here. I wrote about just how inept Newt Gingrich was for not submitting enough valid signatures to get on the Republican Primary in Virginia here.
Ironclad, a 2011 film starring Paul Giamatti, has more on screen
violence and nudity than Braveheart and presents an look at a time in
history of which I was previously unaware. The film is set in the early 13th century and King
John of England (Giamatti) is pissed because he had to sign over a small amount
of power to the barons of England by putting his... well.. pre-John
Hancock on the Magna Carta.
The Sword is Actually Bigger in the Movie
Here are some of the comments I made while watching this film for the first time:
“Do the other side too just so they match” I said just before the drunk gets the wound on his shoulder cauterized.
"Ye olde scrolls for sale! Ye olde
scrolls for sale! Oh, is this not the renaissance faire?" I said when I caught a glace of the camp of King John's army.
"Iron wool!" I exclaimed when the film shows the heroic Templar Knight sharpening his sword.
Maybe you had to be there.
In any event, I highly recommend Ironclad to anyone who enjoys a bloody sword slashing movie.
The only downside to the movie is that it isn't a big budget film and I think it runs about 15 minutes too long. I would have liked to have some of the "that-person-is-lying-on-the-ground-and-not-moving-so-they-must-be-dead... oh-wait-they're-still-alive, yay!" moments and other tropes to have been cut.
Here is the link to where you can watch Ironclad instantly if you're hooked up with the whole Netflix streaming thing.
Overheard Conversation
"I tape it (Say Yes to the Dress) and watch it in the morning."
Really? Can you even still buy blank VHS tapes in the age of TiVo and the DVR?
VHS tapes are so old I don't even know how to draw them anymore.
