QUESTION

Here’s an example an example of Data Parsing in Excel. Phil sent in this challenge. He receives a report and it’s not useful the way it is.

Often, these reports are generated for an executive to read. That same report gets turned over to an Analyst to use to extract information from and make calculations.

So, how do you get from a “Report” to a sortable list?

Here is the original format:

(The information used here is bogus in order to protect sensitive data.
The essence of the problem remains intact.)

 DESIRED RESULT

In order to get from DO NOT WANT to DO WANT there’s a lot of junk in the way that Phil has to manually move or extract. He’s feeling his way through a maze to get where he needs to be. Not only is this tedious and time-consuming, the manual effort increases the chance for errors.

Before we jump into a solution, there are some things to observe to help develop a strategy for Data Parsing in Excel:

STRATEGY

• if we can get the states peeled out and moved next to the right names,
• we could do a sort, and
• separate the data we need and the data we don’t need.

ANSWER

THE RESULTING FORMULA     =IFERROR(IF(SEARCH(“STATE”,B8),B8,A7),IF(B8=””,””,A7))

When asked what it takes to learn Excel and be good at it, one skill is to be able to explain in English what a formula expresses. Put your hat on ‘cuz here we go!

• If the cell to the right contains STATE,
• then copy everything in that cell
• If that cell does not contain STATE,
• then copy everything in the cell immediately above.
• If there’s nothing in the cell above,
• don’t give me an error message, instead,
• If the cell to the right is blank,
• then give me the blank, otherwise
• give me what’s in the cell immediately above

NOTE: the video is quick. So, please ask any questions in the comments section or the Contact page

Thanks Phil! I hope this helps. Please ask any questions and keep on keepin’ your data clean!

Maze photo credit: FutUndBeidl via photopin cc