---------- Forwarded message ----------
From: My Online Training Hub <webs...@myonlinetraininghub.com>
Date: Wed, Jan 8, 2014 at 5:33 PM
Subject: Excel Newsletter - Excel TEXT Function – handy but limited…or is
it?
To: Kuldeep <naukrikuld...@gmail.com>
Excel's TEXT function is handy but use it with caution, and
occasionally to wow your colleagues! View this tutorial in your
browser.<http://myonlinetraininghub.us1.list-manage2.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=4da5564417&e=d97b5f08e8>
Hi Kuldeep,
*Happy New Year!*
*Phil and I had a lovely break and spent some quality time with our boys.
As it’s still summer holidays in Australia they’ve not started school for
this year yet, so we’ll be juggling work and being child entertainers for
the next few weeks. Wish us luck!*
*I’m excited to be back and writing my weekly Excel Newsletters again and I
can’t wait to get stuck into working on our plans for 2014.*
Excel TEXT Function – handy but limited…or is it?
At first glance the TEXT function appears quite limited. Its purpose is to
convert numbers to text in a specific/custom format.
There are two key points here:
1. The numbers become text. This means you can’t use them in any math
type of formulas.
2. On the upside you can format the numbers anyway you want…almost!
Hold up!
Why would you want to convert a number into text when it means you then
can’t use that number in any math calculations/formulas?
Wouldn’t you just format the cell with the number format you want?
The answer is ‘yes’ most of the time, but *I’ll show you a clever use for
it* in a moment.
First, the syntax is:
*TEXT(value, format_text)*
Where the *value* is the number or reference to the cell containing the
number you want to format, and *format_text* is the format you want,
enclosed in double quotes.
Download Workbook
Download the Excel
Workbook<http://myonlinetraininghub.us1.list-manage1.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=4f0895354d&e=d97b5f08e8>.
Note: this is a .xlsx file please ensure your browser doesn't change the
file extension on download.
Let’s look at some examples.
TEXT Formula Examples [image: Examples of Excel TEXT formulas]
*Example 1* – Format as a Currency.
[image: Excel TEXT function example 1]
If you don’t know the character codes to use you can refer to the Format
Cells dialog box (CTRL+1 to open it) to get the number format ‘code’ (that
is the $#,##0.00 part) you need:
[image: Examples of Excel TEXT formulas]
You can use any number formats for the *format_text* argument that you find
in the Format Cells dialog box under the ‘Number’ tab.
Or you can write your own custom format.
If you're new to custom number formats check out Jon von der Heyden’s
tutorial for a comprehensive guide to custom number
formats<http://myonlinetraininghub.us1.list-manage.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=31223fd7b2&e=d97b5f08e8>
.
*Example 2* – Formatted as a date with hyphens instead of the Excel
standard forward slash.
[image: Excel TEXT formula example 2]
Note: 41640 in cell A5 is the serial number for the date 1st Jan 2014. More
on understanding dates in Excel
here<http://myonlinetraininghub.us1.list-manage2.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=cfc108b2f9&e=d97b5f08e8>
.
*Example 3 *– Display day of the week.
[image: Excel TEXT formula example 3]
Again we’ve taken a date in cell A6 and with the TEXT function we’ve only
displayed the day of the week. If you wanted to display the full name of
the day you would use “dddd”.
*Example 4* – Add leading zeros to a number.
[image: Excel TEXT formula example 4]
This is handy for invoice numbers or other numbers/codes that require a
leading zero.
Limitations of TEXT Formulas
As I mentioned at the beginning, the above examples all convert the numbers
in column A to text in column B.
Examples 3 and 4 are ok formatted as text (the day of the week and leading
zeros) as they are unlikely to ever be used in a math formula so having
them as text won’t cause you a problem.
But it’s not ideal for examples 1 and 2 (format as currency and date) as
it’s likely you’ll want to perform a calculation using those numbers at
some point in your worksheet’s life.
A better option for examples 1 and 2 is to just apply the number format to
the cell in column A.
Combine Text and Numbers in One Cell
I think the best use of the TEXT function is to use it to combine text and
numbers in one cell.
As you can see in cell A16 below:
[image: combine text and numbers in one cell using TEXT function]
In the formula bar you can see I’ve used the ampersand symbol (&) to
combine the text, “Sales up” and “Since 2010”, with the number returned by
the TEXT function, which I've formatted as a percentage:
="Sales up "&TEXT(B14/B11-1,"0%")&" Since 2010"
Which results in one text string:
Sales up 95% Since 2010
You’ll notice in this example I’ve also performed a calculation for the
*value* argument of the TEXT function, which is then formatted as a
percentage:
TEXT(B14/B11-1,"0%")
Which results in:
95%
The benefit of using a formula for the *value* argument is that any changes
in the data will automatically be reflected in my statement in cell A16.
Bonus Trick
Now you can link cell A16 to your chart title and not only will the chart
update if the numbers change, but so will the title:
[image: dynamic chart title]
To link the chart title to cell A16:
1. Left click the chart title to select it
2. In the formula bar enter = then click on cell A16
3. Press ENTER
Crazy TEXT Function Trick
At the very beginning I said the TEXT function converts a number to text
which means you can’t perform any math calculations on the results.
However, in this video Mike Girvin, of Excel Is Fun, shows a crazy trick
(at 3:30) with the TEXT function to convert a date into the right format.
Even MrExcel doesn’t believe it will work:
<http://myonlinetraininghub.us1.list-manage.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=f9d9dd37d2&e=d97b5f08e8>
*Explanation*: in the video Mike performs a calculation on the text
function which in turn converts the value in the cell back to a number.
Similarly, you can also convert a cell containing a text function back to a
number by multiplying the cell by 1, or add 0, or use the VALUE function,
to name a few.
*Note:* there are some limitations to this. For example you can’t convert
the text 'Wed' in example 3 above back to a number, but you can for the
other examples.
Please Share
If you liked this or know someone who could use it please click the buttons
below to share it with your friends on LinkedIn, Google+1, Facebook and
Twitter.
<http://myonlinetraininghub.us1.list-manage.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=9459dc6362&e=d97b5f08e8>
<http://myonlinetraininghub.us1.list-manage.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=4fa9f64eaa&e=d97b5f08e8>
<http://myonlinetraininghub.us1.list-manage.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=503f6bcbec&e=d97b5f08e8>
<http://myonlinetraininghub.us1.list-manage1.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=2ccf96f282&e=d97b5f08e8>
Have a great day,
Mynda
Co-founder and Head Excel Nerd
My Online Training Hub
Too Advanced?
If this is too advanced a great starting point is our Excel Formulas
List<http://myonlinetraininghub.us1.list-manage1.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=6b71ac013e&e=d97b5f08e8>of
tutorials. 20%
off for My Online Training Hub Members
<http://myonlinetraininghub.us1.list-manage1.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=f7ac778fb2&e=d97b5f08e8>
Excel
Shortcuts for Mac Keyboard Cover
• *20 Excel keyboard shortcuts* for formatting, navigation, cell selection,
and more
• *5 different colors* to make your keyboard stand out from your peers
• *Durable silicone rubber* is easy to type on and protects your keyboard
from dirt and dust
• *Reduces typing noise* from the keyboard
• *Compatible* with Mac Excel 2008/2011 and the latest generation Macbook
Airs, Pros, and Retinas
• *Click here
*<http://myonlinetraininghub.us1.list-manage1.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=a42056df86&e=d97b5f08e8>to
check out their store today for all the different colors.
• *To get 20% off *your Excel shortcuts keyboard cover simply quote *discount
code*: *traininghub2014* Can't Get Enough Excel
Browse the
blog<http://myonlinetraininghub.us1.list-manage.com/track/click?u=47d3fb3986ef1634c2797a0f4&id=8eaba69d33&e=d97b5f08e8>for
more gems like these.
And if you want to search for something specific use the 'Search' box in
the top right of the website. *Copyright © 2014 My
Online Training Hub, All rights reserved.*
You are receiving this email because you either purchased one of our
products, signed up for our free training or opted in to receive our
newsletter.
*Our mailing address is:*
My Online Training Hub
PO Box 7206
Sippy Downs, Queensland 4556
Australia
Add us to your address
book<http://myonlinetraininghub.us1.list-manage.com/vcard?u=47d3fb3986ef1634c2797a0f4&id=b5252539e1>
unsubscribe from this
list<http://myonlinetraininghub.us1.list-manage.com/unsubscribe?u=47d3fb3986ef1634c2797a0f4&id=b5252539e1&e=d97b5f08e8&c=5569e8aefc>
update subscription
preferences<http://myonlinetraininghub.us1.list-manage.com/profile?u=47d3fb3986ef1634c2797a0f4&id=b5252539e1&e=d97b5f08e8>
--
Regards,
Kuldeep Singh
Assistant Manager Quality
Game Zone India Limited
Phone.: +91-8586978644
|| naukrikuld...@gmail.com ||
*Please* *Consider the environment. Please don't print this e-mail unless
you really need to.*
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
