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. 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

Examples of Excel TEXT formulas

Example 1 – Format as a Currency.

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:

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.

Example 2 – Formatted as a date with hyphens instead of the Excel standard forward slash.

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.

Example 3 – Display day of the week.

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.

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:

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:

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:

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.

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *

Comments

  1. Allen Reidhead says

    Excellent instruction – So clear and precise.
    Now what about aligning text boxes in a chart that are setup vertically on right side of chart as column. I am in Dashboard course with Avg Cost numbers on right side of chart, and am unable to align them.

    • Mynda Treacy says

      Thanks, Allen :-)

      You’ll find the alignment tools for text boxes in the ‘Drawing: Format’ tab of the ribbon which is active when the text box is selected. So, select all of your text boxes that you want to align, then in the Format tab: Arrange group use the Align tools.

      To select multiple text boxes just select 1 and then hold down SHIFT to select more. You can also select 1 and then press CTRL+A to select all objects, then you can unselect the ones you don’t need by holding down SHIFT while you click on the ones you don’t want.

      You might also like this free add-in from Excel Campus that allows you to use arrow keys to align the chart elements:

      http://www.excelcampus.com/keyboard-shortcuts/chart-alignment-add-in/

      Mynda

  2. Jon Acampora says

    Thanks for the great article! That’s one crazy trick that Mike Girvin figured out.

    This is one format I use quite often to convert dollars to millions.

    =TEXT(A4,”$#,##0,,.0″)&”M”

    • Mynda Treacy says

      Cheers, Jon.

      You can also add the ‘M’ inside the formula like this:

      =TEXT(A4,"$#,##0.0,,\M")

      Kind regards,

      Mynda.