The Wizard of Excel

Up front, you should know that I have been hacking, in the arcane sense of that word, around with spreadsheets since Lotus 1-2-3. I am self taught, with all of the limitations implied; in other words, sometimes I know what I don’t know, other times I don’t know what I don’t know, and the rest of the time I stumble around in total foggery.

But sometimes little nuggets of understanding fall from the sky and land in my lap. Today was such a day.

I exported some data from a SQL database to Excel, for the purpose of finding missing pieces of information (does that make sense?). In other words, I had to see what I had in order to know what I didn’t have. The spreadsheet was 6 columns and 3,789 rows, not too big in the hands of a power user, but plenty unwieldy for me. I put the old auto filter to work to begin to sort the pieces of data that I was looking for. It quickly became clear that my data was buried within a column that contained multiple data types. It could not be separated by a sort or a filter.

Stymied again was my first thought, defeated again by the complexities that lay beyond my understanding.

But then I grasped that the data was actually text, and that the particular data set that I needed was made up of 4 characters or less, while the unwanted data had either 5 letters or numbers. I recalled that in my programming classes we had used a function (Len) to measure the length of a string and either slice or concatenate text to suit our needs. Could Excel have such functionality?

Of course it does:

Len(a2)                         returns the length of the string in cell a2.

Further, you can tell Excel to count from the left, right, or middle. All I needed to do was tell Excel to give me the length of the string, starting from the right of the cell. From a source found on the web, called Tech Republic:

Figure A

We’ll use functions to extract certain portions of the entries in column A.

Those strings contained three distinct parts:

  • The first three characters (the K-numbers) represent a product code.
  • The second two digits (the B-numbers) represent a price code.
  • The final three digits represent a customer code.

My coworker wanted to separate out those three pieces into different columns, and she was retyping them from scratch! That approach is so wasteful and inefficient it makes my skin crawl. Fortunately, I got to be the hero by showing her how to use Excel’s string functions to extract the codes automatically.
Fun with string functions
All of you veteran spreadsheet users know this drill by heart. Here’s how it works.
Grabbing the first three characters. To extract the first three characters of the text entries, you enter the Left function like this:
=Left(source_string,number_of_characters)
In this case, we entered into cell B2 the function =Left(A2,3) and then copied that formula to cells B3:B8. Figure B shows the results.

Figure B

The Left function eliminates the need to re-key the first three letters from the entries in column A.

Pulling out the two characters in the middle. To extract the two characters in the middle of the string, we’ll use the Mid function, which takes the form:
=Mid(source_string,start_position,length)
Since we know that the string we want to extract always starts in position 4, we entered into cell C2 the function =Mid(A2,4,2) and then copied that formula to cells C3:C8. Figure C shows the results.

Figure C

The Mid function lets you pull a string out of the middle.

Extracting the last three characters of a string. In order to extract the last three characters of a string, you use the Right function in the form:
=Right(source_string,number_of_characters)
In our example, we entered in cell D2 the function =Right(A2,3) and copied it into cells D3:D8. As Figure D shows, that function returns the three rightmost characters in the source string.

Figure D

The Right function makes it easy to copy a set of characters from the right side of a string.

Once you’ve extracted the strings, then what?
After you’ve used the string functions to parse the source string into substrings, you’re free to sort or subtotal your data on any of the columns that contain those substrings. It only takes a minute or two to compose the function call and copy it to the appropriate cells. This technique comes in handy when you’re importing text files that have dumped from a mainframe database or from some other application.

Pretty cool, huh? So all I had to do was put the string search in an adjacent cell, and then run a simple if statement….

An explanation of the if statement in Excel:

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to conduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test     is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true     is the value that is returned if logical_test is TRUE. For example, if this argument is the text string “Within budget” and the logical_test argument evaluates to TRUE, then the IF function displays the text “Within budget”. If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false     is the value that is returned if logical_test is FALSE. For example, if this argument is the text string “Over budget” and the logical_test argument evaluates to FALSE, then the IF function displays the text “Over budget”. If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

So, by now you’ve probably got it figured out. I used the Len function to count the text in each cell of the column, and then I used an if statement to copy the text I wanted into yet another column, while not printing the text that I did not want. And, it worked!

To say that I felt a sense of achievement would be a gross understatement.

What a day for your scribe – a nice mix of programming techniques mixed with a deeper understanding of Excel, the combination of which is at least as satisfying as a nice, dry martini and a Fuente 8-5-8.

Yea Me!

A New Look

Please enjoy the new look of Agricola. We’ve updated the theme (WordPress Twenty Eleven), adjusted the sidebar to give things a cleaner look, and added tools to take advantage of social media. What this means is that now you can sign up to receive an email notification when a new post is up, and that posts are sent to Twitter and Facebook for those of you that prefer to receive our missives via those media. There are a few tweaks that need to take place behind the curtain, but they should be transparent to the readership. If, however, you come to the site and see some sort of error message, well, then we have screwed the pooch.

Thanks must go to Charleston Today’s superb technical support, who guided this transition with relative ease and an understanding of html and css that elude your humble scribe.

Let the posting begin!

Synthetic Biology

These are either the scariest words ever written or the announcement of our impending immortality…

"We make a genome from four bottles of chemicals; we put that synthetic genome into a cell; that synthetic genome takes over the cell," said Dr. Gibson. "The cell is entirely controlled by that new genome."

The scientists didn’t give the new organism its own species name, but they did give its synthetic genome an official version number, Mycoplasma mycoides JCVI-syn1.0.

To set this novel bacterium—and all its descendants—apart from any natural creation, Dr. Venter and his colleagues wrote their names into its chemical DNA code, along with three apt quotations from James Joyce and others. These genetic watermarks will, eventually, allow the researchers to assert ownership of the cells. "You have to have a way of tracking it," said Stanford ethicist Mildred Cho, who has studied the issues posed by the creation of such organisms.

In case you missed it, scientists working for Craig Venter have created, for the first time, a completely synthetic organism by writing computer code to create the desired gene sequences, made the DNA from the code, and then transplanted the DNA in an empty cell, which was taken over by the DNA.

This is literally a turning point in the relationship between man and nature," said molecular biologist Richard Ebright at Rutgers University, who wasn’t involved in the project. "For the first time, someone has generated an entire artificial cell with predetermined properties.

Read the whole story here and ponder what this means for mankind.

The Fork in the Road

"Life is all about choices" – Wise Old Man

"When you get to a fork in the road, take it" – Wise Old Baseball Manager

 

Two roads diverged in a yellow wood,
And sorry I could not travel both
And be one traveler, long I stood
And looked down one as far as I could
To where it bent in the undergrowth;

Then took the other, as just as fair
And having perhaps the better claim,
Because it was grassy and wanted wear;
Though as for that, the passing there
Had worn them really about the same,

And both that morning equally lay
In leaves no step had trodden black
Oh, I kept the first for another day!
Yet knowing how way leads on to way,
I doubted if I should ever come back.

I shall be telling this with a sigh
Somewhere ages and ages hence:
two roads diverged in a wood, and I —
I took the one less traveled by,
And that has made all the difference.

Robert Frost

The Dip

Sisyphus
Sisyphus

A copy of Seth Godin’s new book , The Dip, fell into my hands a few days ago. Like a lot of people, I think Seth is a very smart guy. His blog is a source of wonderful insights and great ideas.

The Dip is about knowing when to quit something, and knowing when to push forward. And as I move through the process of achieving my dream, his words are timely and encouraging.

Here is a snippet that caught my eyes and immediately struck me as The Truth…

Almost everything in life worth doing is controlled by The Dip.

At the beginning, when you first start something, it’s fun. You could be taking up golf or acupuncture or piloting a plane or doing chemistry – doesn’t matter; it’s interesting, and you get plenty of good feedback from the people around you.

Over the next few days and weeks, the rapid learning you experience keeps you going. Whatever your new thing is, it’s easy to stay engaged in it.

And then the Dip happens.

The Dip is the long slog between starting and mastery. A long slog that’s actually a shortcut, because it gets you where you want to go faster than any other path.

The Dip is the combination of bureaucracy and busywork you must deal with in order to certified in scuba diving.

The Dip is the difference between the easy “beginner” technique and the more useful “expert” approach in skiing or fashion design.

The Dip is the long stretch between beginner’s luck and real accomplishment.

The Dip is the set of artificial screens set up to keep people like you out…

…It’s easy to be a CEO. What’s hard is getting there. There’s a huge Dip along the way. If it was easy, there’d be too many people vying for the job and the CEOs couldn’t get paid as muc, could they? Scarcity, as we have seen, is the secret to value. If there wasn’t a Dip, there’d be no scarcity.

IMPORTANT NOTE: Successful people dodn’t just ride out the Dip. The don’t just buckle down and survive it. No, they lean into the Dip. They push harder, changing the rules as they go. Just because you know you’re in the Dip doesn’t mean you have to live happily with it. Dips don’t last quite as long when you whittle at them.

I’m in my own Dip right now. It has forced me to question my goals and dreams, to doubt my abilities, to wonder if I have made a mistake. From a confident, aggressive seeker of new knowledge, I have shrunk slightly and moved into a defensive crouch.

I understand now that this is the time to become more aggressive, to confront the challenges, to re-assert my determination to reach my goal.

Thank you, Seth