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.

Learn Excel Dashboard Course

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





Cell Formula
A1 Text
A2 123/21 Sample Text Left Placement
A3 Sample Text Right Placement 145/335
A4 Sample 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

Learn Excel Dashboard Course
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



Cell Formula
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:

Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

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









Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

LEAVE A REPLY

Please enter your comment!
Please enter your name here