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

Let’s look at some examples.

TEXT Formula Examples

Example 1 – Format as a Currency.

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:

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.

Note: 41640 in cell A5 is the serial number for the date 1^st Jan 2014. More on understanding dates in Excel here.

Example 3 – Display day of the week.

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.

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:

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:

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+, Facebook and Twitter.