The Text function converts the value to text and displays the value, using the specified format.
Text([Value], [Format])
Where:
Value is the number, or reference to the number, that you want to convert to text and display in the specified format.
Format is the format string as described in the following sections, which describe how the numeric value should be displayed.
Rule | Meaning |
|---|---|
| Text(NumberTextBoxReturn, "$0.00") | The Text function converts the return value from the NumberTextBox, into text and applies the currency formatting "$0.00", to the value. |
NumberTextBoxReturn Value | Format | Result of the Text function |
|---|---|---|
| 234.8343 | "$0.00" | $234.83 |
| 0.14 | "0.00%" | 0.14% |
| 12/03/07 | "dd/mmm/yyyy" | 12/Mar/2007 |
In order to display the specified value as a number format, you can use a combination of the following digit placeholders and include them in the Format field.
Digit Placeholder | Description | Example Formula | Example Result |
|---|---|---|---|
| 0 | Displays insignificant zeros if the value has less digits than the format specifies. If the value as more digits than there are zeros then the value will be rounded to the number of zeros in the format. | TEXT(7.5, "0.00") | "7.50" |
| TEXT(7.23218, "0.00") | "7.23" | ||
| TEXT(7.5, "00.0") | "07.5" | ||
| TEXT(75.6, "0.00") | "75.60" | ||
| # | Is the same as the 0 placeholder, except it does not display extra zeros if there are less digits than the format specifies. | TEXT(1.5, "#.##") | "1.5" |
| TEXT(1.67, "#.##") | "1.67" | ||
| TEXT(1.23446, "#.###") | 1.234 | ||
| ? | Is the same as the 0 placeholder, except when there are less digits than the format specifies, it adds a space instead of a zero. | TEXT(3.45, "??.00") | " 3.45" |
| TEXT(45.2, "???.0") | " 45.2" | ||
| TEXT(23.546, "??.??") | "23.55" | ||
| TEXT(12.8, "??.??") | "12.8 " | ||
| . (Period) | Displays a decimal point in the value. | TEXT(234, "0.00") | "234.00" |
| TEXT(12, "0.0") | "12.0" | ||
| , (Comma) | Displays the thousands separator in the value. | TEXT(89000, "#,###") | "89,000" |
| TEXT(22000, "#,") | "22,000" | ||
| TEXT(76500, "0,0.0") | "76,500.0" |
To display the specified value using a Date format, you can use a combination of the following Date Codes, and include them in the Format field.
Date Codes | Description | Example Formula | Example Result |
|---|---|---|---|
| d | Displays the single digit number value of the day, so doesn't include the leading zero. | TEXT("02/04/2008","d") | "2" |
| dd | Displays the two digit number value of the day, so includes the leading zero. | TEXT("02/04/2008", "dd") | "02" |
| ddd | Displays the three letter abbreviation of the day. | TEXT("02/04/2008", "ddd") | "Wed" |
| dddd | Displays the full name of the day. | TEXT("02/04/2008", "dddd") | "Wednesday" |
| m | Displays the single digit number value of the month, so doesn't include the leading zero. | TEXT("02/04/2008", "m") | "4" |
| mm | Displays the two digit number value of the month, so includes the leading zero. | TEXT("02/04/2008", "mm") | "04" |
| mmm | Displays the three letter abbreviation of the month. | TEXT("02/04/2008", "mmm") | "Apr" |
| mmmm | Displays the full name of the month. | TEXT("02/04/2008", "mmmm") | "April" |
| yy | Displays the two digit number value of the year. | TEXT("02/04/2008", "yy") | "08" |
| yyyy | Displays the four digit number value of the year. | TEXT("02/04/2008", "yyyy") | "2008" |
Note: The "m" and "mm" format in the Date Formats represent the value of the month. In the Time Formats the "m" and "mm" formats represent the value for minutes.
When the result of a Date Picker control is required to be formatted, the control name can be used directly in the Value field.
For example, to extract the year:
TEXT(DatePicker1Return, "yyyy")
Note quotes are not required around the Value field.
To display the specified value using a Time format, you can use a combination of the following Time Codes, and include them in the Format field.
Time Codes | Description | Example Formula | Example Result |
|---|---|---|---|
| h | Displays the single digit number value of the hour, so doesn't include the leading zero. | TEXT("09:07:02", "h") | "9" |
| hh | Displays the two digit number value of the hour, so includes the leading zero. | TEXT("09:07:02", "hh") | "09" |
| m | Displays the single digit number value of the minute. The m format must be used either after one of the hour formats, or before one of the second formats, otherwise the month is returned. | TEXT("09:07:02", "hh:m") | "09:7" |
| mm | Displays the to digit number value of the minute. The mm format must be used either after one of the hour formats, or before one of the second formats, otherwise the month is returned. | TEXT("09:07:02", "mm:ss") | "07:02" |
| s | Displays the single digit number value of the second, so doesn't include the leading zero. | TEXT("09:07:02", "s") | "2" |
| ss | Displays the two digit number value of the second, so includes the leading zero. | TEXT("09:07:02", "ss") | "02" |
| [h] | Displays the number of hours that have elapsed. | TEXT("02:34:28", "[h]:[mm]") | "[2]:[34]" |
| [m] | Displays the number of minutes that have elapsed. | TEXT("03:05:56", "[hh]:[m]") | "[03]:[5]" |
| [s] | Displays the number of seconds that have elapsed. | TEXT("9:36:06", "[mm]:[s]") | "[36]:[6]" |
| AM/PM
am/pm A/P a/p | Displays the hour, using the twelve hour clock. | TEXT("13:35:40", "hh:mm:ss AM/PM") | "01:35:40 PM" |
In order to display the specified value using a currency format, you can use the following currency symbols and include them in the Format field. If you want to enter a currency symbol using the key combination, make sure you press the NUM LOCK key and use the numeric keypad to type in the combination.
Currency Symbol | Description | Example Formula | Example Result |
|---|---|---|---|
| $ | Displays the number with a dollar sign. | TEXT(56.89, "$0.00") | "$56.89" |
| £ | Displays the number with a pound sign. | TEXT(32.87, "£#.##") | "£32.87" |
| € | Displays the number with a euro sign. | TEXT(84.02, "€0.00") | "€84.02" |
| ¢ | Displays the number with a cent sign. | TEXT(34, "0¢") | "34¢" |
| ¥ | Displays then number with a yen sign. | TEXT(200, "¥0") | "¥200" |
You can display the specified value as a percentage of 100 by including the percentage symbol in the Format field.
Decimal places need to be taken into account in the Format string.
Percentage Symbol | Description | Example Formula | Example Result |
|---|---|---|---|
| % | Displays the specified value as a percentage. | TEXT(0.34, "0.00%") | "0.34%" |
| % | Displays the specified value as a percentage. | TEXT(15, "0%") | "15%v |
| % | Displays the specified value as a percentage. | TEXT(10.2, "0.0%") | "10.2%" |
In order to display the specified value as a fraction format, you can use the following fraction formula or a variation of the fraction formula, and include it in the Format field.
Fraction Formula | Description | Example Formula | Example Result |
|---|---|---|---|
| ?/? (numerator)/(denominator) | Displays the specified value in fraction format. The number of question marks used for the denominator, determines the maximum amount of digits that could be displayed in the denominator. | TEXT(0.9, "?/?") | "8/9" |
| TEXT(0.9, "?/??" | "9/10" | ||
| TEXT(0.9, "?/???") | "9/10" | ||
| TEXT(0.001, "?/?") | "0/1" | ||
| TEXT(0.001, "?/?????") | "1/1000" |
The characters in the table below can be included in the Format field and will be displayed exactly as they are.
Note: The * (asterisk) character must not be entered in the Format field, otherwise a #VALUE! result will be returned.
Character Symbol | Character Name |
|---|---|
| + | Plus Sign |
| - | Minus Sign |
| / | Slash Mark |
| ( | Left Parenthesis |
| ) | Right Parenthesis |
| : | Colon |
| ^ | Caret |
| < | Less Than Sign |
| > | Greater Than Sign |
| = | Equal Sign |
| { | Left Curly Bracket |
| } | Right Curly Bracket |
| ! | Exclamation Mark |
| ~ | Tilde |
| & | Ampersand |
| Space | |
| $ | Dollar Sign |