Getting to Know Excel Text Fuctions and Functions

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
1Text
2123/21 Sample Text Left Placement
3Sample Text Right Placement 145/335
4Sample Text 99/34 Middle Placement

Sheet1

Worksheet Formulas





CellFormula
A1Text
A2123/21 Sample Text Left Placement
A3Sample Text Right Placement 145/335
A4Sample Text 99/34 Middle Placement

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

AB
1TextRanking
2123/21 Sample Text Left Placement21
3Sample Text Right Placement 145/335335
4Sample Text 99/34 Middle Placement34

Sheet1

Worksheet Formulas



CellFormula
B2=VALUE(MID(A2,FIND(“/”,A2)+1,3))
B3=VALUE(MID(A3,FIND(“/”,A3)+1,3))
B4=VALUE(MID(A4,FIND(“/”,A4)+1,3))

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









LEAVE A REPLY

Please enter your comment!
Please enter your name here