While I worship so many functions and capabilities in Excel, I must admit, I am a true sucker for Text functions.
My love for Text functions started during my oh so sexy Reinsurance career where I was often tasked with some serious data cleansing.
Excel’s various Text functions are useful when it comes to manipulating, extracting, removing and replacing text strings from cells to put them in a format that works for you.
If you want to dig deeper into Text functions, check out our 3rd Newsletter/Blog post from 2009. Also, you can download our Text Functions lesson on the Tips & Tricks page of www.ExcelRainMan.com.
To expand on this lesson, we are going to review the actual =TEXT function. Clearly I dropped the ball not including this in our earlier all-encompassing Text functions handout… My B.
So here’s how it works:
The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This allows you to display numbers in a more readable format, which I find particularly useful when combining:
– numbers with text or symbols
– the contents of multiple cells
Syntax: =TEXT(value, format_text)
where:
value: is a numeric value… typically a cell reference
format_text: is the formatted text string in quotation marks indicating how you want the value to be shown
Basic Examples:
If cell A1 is 2.5
=TEXT(A1,”$0.00″)
Result is $2.50
=TEXT(A1,”0.00%”)
Result is 250.00%
Don’t forget to put the quotes in the formula!!
Here is a quick map to show some of the ways the format_text should appear in your formula to display text in the following manner:
Hard Core Example: using MIN, MAX, & and TEXT functions to show a properly formatted percentage range in a cell… also included is a little bit of formula nesting!
In the below worksheet, we see NFL Quarterbacks’ passing completion percentage per week in 2011:
To see their best and worst week’s completion percentage, we use the MIN and MAX functions in columns V and W:
We will then use the & Function to combine the Minimum and Maximum Percentages into a range:
Notice that not only does our Range column not show percentages, but it also has way too many decimals for the Minimum and Maximum percentages.
To show the range as a percentage with no decimal points, we will change the formula in column X starting with using the TEXT function around the Min percentage (cell). The additions to the formula are in black:
We can now do the same thing with the Max percentage (cell). Again, the changes to the formula are in black:
Then copy and paste the formula down:
Football Survivor Pool
Although our Football Survivor Pool downloads have been record breaking this year, we worry that there are still Pool Managers out there dealing with the administrate hassles of running a pool without any PES (Performance Enhancing Spreadsheets that is). It is not too late to manage your pool using the FREE Excel Rain Man Football Survivor Pool. Download it today or tell your Pool Manager to get all up on it!
More details on the FREE Excel Rain Man Football Survivor Pool can be found here.
Are your spreadsheets acting 50 shades of crazy? Contact Excel Rain Man and we will show them who’s boss!