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.
|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")
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.
- Published: 17 August 2014
- Revised: Monday 24th of February 2020 - 11:12 AM, [Australian Eastern Standard Time (EST)]