Text function


This module demonstrates the Excel TEXT function with application to dates, percentages, currency formats, and numbers.

  • SYNTAX: TEXT(value, format_text)

The TEXT function is normally used in conjunction with a concatenation procedure, or in a dynamic format setting with data validation used as the selector. To format a cell use Home > Number > Format Cells > Number on the ribbon.

Selected Excel functions used in this module.

Excel functions Description
CHAR(number) Returns the character specified by the code number
DATE(year,month,day) Returns the serial number of a particular date
DOLLAR(number,decimals) Converts a number to text, using the $ (dollar) currency format
TEXT(value,format_text) Formats a number and converts it to text
TODAY() Returns the serial number of today’s date
YEAR(serial_number) Converts a serial number to a year

Text function worksheet

In the Text function worksheet (figure 1, Excel Web App #1), the seed date in cell C3 uses single digits for month and day. This allows d cf. dd (rows 13 and 14) and m cf. mm (rows 17 and 18). A concatenation example is shown in row 29.

The currency examples in rows 57 to 64 are mostly based on the dollar. The Yuan symbol is illustrated in row 62 and 63, with the Excel CHAR function returning the currency symbol.

Return values for the GetCF function have been converted to text because of the xlsx file format.

The comment cell for the Time: example contains an alternative to the GetCF, namely =IFERROR(ADDRESS(ROW(),COLUMN(Time),4,1)&": "&FORMULATEXT(Time),"Requires Excel 2013 or later")

Fig 1: Excel Web App #1: TEXT function with Date, Percentage, Currency and Number Abbreviation

Related material

The worksheet CHAR function

  • Download the file by using the Microsoft Excel Web App link in figure 1.
  • This module was developed in Excel 2013 Pro 64 bit and made available via the OneDrive Excel Web App.