The Archives

Browse the content below to find what you're looking for.

Peeling Data out of the Center of a String

Wednesday, November 3rd, 2010

This morning I was presented with a challenge. My friend had 3,500 lines of data that looked something like this:


  1. P-ADAMSRES-0253_P-ADAMSRES-0253-001_Base_Fixed
  2. P-AESC-1421_P-AESC-1421-001_Base_IF NGPL, Tex_1
  3. P-AMGC-0028_P-AMGC-0028-001_Base_Fixed
  4. P-ATMOS-0010_P-ATMOS-0010-001_Base_Nymex Last_1
  5. P-BARBANK-0075_P-BARBANK-0075-001_Base_Fixed

She needed the second occurrence of the “P-” from each line, along with the “-001” or whatever it might be. So from the first line she wanted the string P-ADAMSRES-0253-001 . Had each string been the same length then this would have been simple. Turns out that we can still make this easy, at least easier than hand parsing 3,500 lines one at a time.

The trick is in indentifying unique characters in our data that would tell us where the start or stop of something was. In this case there happens to be an “_” underscore just before our string starts. Perusing a large sampling of the data I can see that this is always the case.

I use the FIND function to locate the position of that “_”. If that string was located in cell A1 then in cell B1 I would type the formula =FIND(“_”,A1) . The function would return the number 16 telling me that the underscore was found starting in the 16th position.

I got a bit lucky with the ending of the string because they all ended with a “-001”, “-002”, etc. The important thing was that it was the same length as the first string but with four characters added to the end.

Using the FIND function I know where the string starts and by using the LEN function I know how many characters to pick up.

My final formula, which I entered in cell C1, was =MID(A1,B1+1,B1+3) .

Download the Example Workbook Now!

Comparing Data Sets

Thursday, October 21st, 2010

Last Thursday I had the privilege of teaching a group of division order analysts about the VLOOKUP function. I consider VLOOKUP to be an essential tool for data manipulation, so I knew it would be the primary function I focused on during our short 55-minute class.

But I also knew that data is often not as neat and clean as I would hope for. I almost always have to tweak some field, and this almost always involves using Text Functions like LEFT, RIGHT, MID, &, LEN, FIND, and REVERSETEXT. So how did I squeeze these into an hour?

The answer is that I didn’t. I chose instead to record tutorial videos and temporarily place them on this site along with the workbooks so that each attendee could practice these at their own pace. I chose to stick with just the VLOOKUP, going over it for about half the hour, then inviting attendees to come to this website for more practice.


I have always known that comparing data can be a difficult thing for most folks. The quintessential wrong, but most often used way to compare data is through ocular inspection. That’s a fancy way of saying that people eyeball two data sets to see if they match. You can probably relate right? If you have ever tried positioning two data sets side by side and then moving one of them up and down to match the other one then you know what I mean.

There’s a better way.  When I began preparing for the 2010 NADOA Conference in San Antonio, I began to think that if I would automate just a bit more, I could come up with a really sweet tool, one that more people could learn how to use.

That thought culminated with a “compare data” feature on TheDataWand.

The featured video shows this addition to TheDataWand at full speed.

Spend your morning relaxing, not gathering data.

Friday, November 13th, 2009

Ever wonder why some folks get into the office at the crack of dawn?

Sometimes it’s because they have something  important that has to be sent to their customers each and every morning, and it takes them a bit of time to prepare whatever that “something” is.

Back in 2003 I helped a Gas Scheduler with her “something”.  This simple four-tab workbook combines SQL with VBA to produce an end result saving her an hour each morning.

The power of being able to extract data directly into your workbook cannot be over emphasized. It is the basis for nearly all my Excel automation.  If the data is the seed then VBA is the water and sunlight. Using the two in concert we have built some awesome homegrown solutions.

In this example a SQL statement is used to extract data from a Sybase database. It posts that data to the “Data” tab where more VBA takes over to write SUMIF functions on the “Baseload” tab. Finally, fixed formulas are waiting on the “Morning Report” tab to provide the user with a daily look at their volumes.

Get The Data Wizard involved with your project and maybe tomorrow you can relax too.

Testimonials

Tuesday, November 3rd, 2009

I am in awe!  That was amazing to watch the automation of a process that takes me forever, be done in under a minute.  Thank you! Melanie F.

Your tools are fast, effective and widely used.  Dennis D.

Thanks for designing the great tool that you did–if you only knew what a life saver it is!!! Cindy D.

I have to say the Excel tools you provided have made custom querying much faster than the final product run from canned Crystal reports.  I can’t tell you how much it has helped in my work.  I consider it an essential tool of the trade.  Thank you greatly for all you’ve done to make it happen. You are so fortunate to possess these amazing talents & technical skills! Kent O.

Thank you for all the help you have given me over the years.  Dan P.

Steve and his query tool have been vital in running our business in recent years. The data that comes back from the query tool has been incorporated into literally dozens of our accounting spreadsheets.  Far and away it is superior to anything that IT has ever provided for us and Steve is always there to answer any questions we might have. Patrick F.

Thanks for all the help you’ve given us the past 5 years – it’s made our work so much easier and much more efficient. Diane W.

Thank you for your efforts in providing and maintaining this query tool which literally cut research and analysis times in half. Don M.

I am excited about what I have learned from you. Kathy F.