Frequently I have to take other data sets and make sense of them. Sometimes there is very little sense of the data, but every once in a while, you may find that the information you seek is in the data.
Recently, I was given data that was about 50 columns and 200 rows. It was mostly text and buried within the text was a ranking number. However, it wasn’t always in the same place. Sometimes it was on the left of the cell text, sometimes in the middle of the cell text and sometimes at the end of the cell text. Here is what it looked like:
Excel 2007
| A | |
|---|---|
| 1 | Text |
| 2 | 123/21 Sample Text Left Placement |
| 3 | Sample Text Right Placement 145/335 |
| 4 | Sample Text 99/34 Middle Placement |
Sheet1
Worksheet Formulas
|
What I needed to to was focus our team on the top 10 priorities. The priority ranking number is the 2 or 3 digit number directly after the “/” (forward slash text). Instead of going through each row and entering the ranking, I created a quick formula to extract the formula using Microsoft Excel Text Functions. These text functions are a must have if you are building Excel dashboards, as you never know how and where your data will be designed and delivered.
Here is the Excel Text Formula that I wrote to extract the ranking:
Excel 2007
| A | B | |
|---|---|---|
| 1 | Text | Ranking |
| 2 | 123/21 Sample Text Left Placement | 21 |
| 3 | Sample Text Right Placement 145/335 | 335 |
| 4 | Sample Text 99/34 Middle Placement | 34 |
Sheet1
Worksheet Formulas
|
Here is a quick video demonstration showing you how this Text Function works:
Can you think of any other way to quickly extract the rank from this data? Let me know in the comments below. Also don’t forget to SUBSCRIBE to my blog as I will have some specials coming to my subscribers this month.
Steve=True
