- The numbers become text. This means you can’t use them in any math type of formulas.
- 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 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 ExamplesExample 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 1st 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 FormulasAs 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 CellI 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 2010You’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 TrickNow 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:
- Left click the chart title to select it
- In the formula bar enter = then click on cell A16
- Press ENTER