Monthly Archives: August 2011

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!

Advertisements

AT&T (Ain’t They Terrible)

customer_service

There is no lesson in the second kick of the mule.

Why is that fundamental truth so hard to accept?

Today, I spent 50 minutes on the wireless with AT&T, trying to find the equipment that was supposed to arrive at our house yesterday, so that we could initiate our new fiber optic internet and landline service. I just knew, last week, when I placed the order with Judy, that things were certain to go awry. Without exploring the details, I’ll stipulate that the last attempt to establish a business relationship with AT&T, more than 8 years ago, was so frustrating, so screwed up, and so difficult to untangle that I swore never again to subject myself to their vast incompetence.

Today, when I called, a gentleman named Paul answered, and after the obligatory rigmarole about my identity, we got down to business. Where was the equipment that I ordered last Wednesday, that supposed to arrive yesterday, for the service that was turned on last night? He didn’t know, but asked that I give him 2 or 3 minutes to track it down. Sure, I said, before hanging up 25 minutes later, having never heard another peep from Paul.

The second call landed me in the helping hands of Elizabeth. After a second bit of rigmarole about my identity, we got down to business. Where was the equipment that I ordered last Wednesday, that supposed to arrive yesterday, for the service that was turned on last night? She didn’t know, but asked that I give her 2 or 3 minutes to track it down. Sure, I said, but don’t treat me like Paul, or Mt. St. Helen might erupt again. Just saying.

5 minutes later, Elizabeth returned with the bad news. The order was supposed to have arrived yesterday, but since it had not, she was going to call UPS, put the delivery on hold, and re-send the equipment. Whoa, said I; let’s give UPS a chance to deliver the equipment this afternoon. Just give me the tracking number and I will not bother your department again. My last problem with you folks started like this, with multiple shipments, lost shipments, return authorizations not received, et cetera. I do not want to go there. Well, she said, I don’t have the tracking number, but let me put you on hold and I’ll get it from the shipping department.

5 minutes later, Elizabeth returned with the bad news. The order had not, in fact, shipped. She didn’t know why, but if I could hold just a little bit longer, she would take the matter up with her supervisor.

10 minutes later, Elizabeth returned with new information. A new order had been placed, and they were shipping it out via 2 day UPS, at no cost to me (?), with arrival on the 12th. Elizabeth, I asked, if it ships today, via 2 days UPS, then it will arrive on Thursday, the 11th, right? Well, she said, it won’t actually ship until tomorrow. I decided not to press the girl, who was obviously struggling with the entire process, thanked her for her help, and asked her to tell Paul to suck it…….

Before I could refill my coffee cup, I received an email from my friends at AT&T confirming my order. It looked exactly like the email I received last week, and look how reliable that was. But something caught my eye, and a closer examination of the email revealed a listing of my account number, order number, and telephone number. Only problem was that the phone number on this email doesn’t match the phone number on the first email.

I can’t wait to have to untangle this mess – two telephone numbers, two bills, different service dates, and God knows what else.

What else could possibly go wrong?

I Hate The Remote

remote_controlLost in the mists of time is the exact date that our family acquired a remote control for the television. It may have come with the advent of cable, or maybe the invention of digital controls. Whenever or whatever, the salient feature was, for my mother, the mute button. After years of commercials, she was now able to shut out the sound of things she did not like. The world, for us children, was forever changed. I hated the silence.

Upon leaving home, I was able to return to the normalcy of awful commercials, uneven sound levels, and the general cacophony of what passes for entertainment. Only when visiting my parents’ house would I be reminded of the mute button. It was enough to make pleasant visits unbearable.

This morning, while reading the paper and drinking my pot of coffee, my wife announced, with great pleasure, that she had found the mute button on the DIRECTV remote. As I struggled to grasp the ramifications of her pronouncement, a silent room took over my consciousness. The television was on, the picture showed a commercial, but no sound came forth. This event re-occurred several times over the next thirty minutes, until finally I could not take it any more. I begged for a cessation of this ritual, an abeyance of the muting practices. To no avail.

It was then that I realized, during a moment of reflection in a silent period, what had happened.

I have married my mother.