DriveWorks Solo 15: Text [send feedback...]

Syntax

TEXT( [ Value], [ Format] )

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.

The TEXT function, when calculated, converts the value to text and displays the value, using the specified format.

Examples

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.

Example Outcomes

NumberTextBoxReturn Value

Format

Result of the TEXT function

234.8343"$0.00"$234.83
0.14"0%"14%
12/03/07"dd/mmm/yyyy"12/Mar/2007

Number Formats

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

0Displays 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"
TEXT(76500, "0.0,""76.5"

Date Formats

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

dDisplays the single digit number value of the day, so doesn't include the leading zero.TEXT(02/04/2008,"d")"2"
ddDisplays the two digit number value of the day, so includes the leading zero.TEXT(02/04/2008, "dd")"02"
dddDisplays the three letter abbreviation of the day.TEXT(02/04/2008, "ddd")"Wed"
ddddDisplays the full name of the day.TEXT(02/04/2008, "dddd")"Wednesday"
mDisplays the single digit number value of the month, so doesn't include the leading zero.TEXT(02/04/2008, "m")"4"
mmDisplays the two digit number value of the month, so includes the leading zero.TEXT(02/04/2008, "mm")"04"
mmmDisplays the three letter abbreviation of the month.TEXT(02/04/2008, "mmm")"Apr"
mmmmDisplays the full name of the month.TEXT(02/04/2008, "mmmm")"April"
mmmmmDisplays the first letter of the month.TEXT(02/04/2008, "mmmmm")"A"
yyDisplays the two digit number value of the year.TEXT(02/04/2008, "yy")"08"
yyyyDisplays 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.

Time Formats

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

hDisplays the single digit number value of the hour, so doesn't include the leading zero.TEXT(09:07:02, "h")"9"
hhDisplays the two digit number value of the hour, so includes the leading zero.TEXT(09:07:02, "hh")"09"
mDisplays 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"
mmDisplays 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"
sDisplays the single digit number value of the second, so doesn't include the leading zero.TEXT(09:07:02, "s")"2"
ssDisplays 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"

Currency Formats

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"

Percentage Format

You can display the specified value as a percentage of 100 by including the percentage symbol in the Format field.

Percentage Symbol

Description

Example Formula

Example Result

%Displays the specified value as a percentage of 100.TEXT(0.34, "0%")"34%"

Scientific Notation Format

In order to display the specified value using scientific notation, you can use one of the following scientific notations and include them in the Format field.

Scientific Symbol

Description

Example Formula

Example Result

E(E+, e+, E-, e-)Displays the specified value in scientific format. E will be displayed with a number to the right of it, that is the number of places the decimal point has moved.TEXT(15000, "0.0E+00")"1.5E+04"
TEXT(15000, "0.0E-00")"1.5E04"
TEXT(0.000034, "#.##E-#")"3.4E-5"
TEXT(0.000034, "#.##E+#")"3.4E-5"

Fraction Format

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"

Ignored Characters

The characters in the table below can be included in the Format field and will be displayed exactly as they are. This means that they do not need to be enclosed in quotation marks, as they are not processed by the formatting string.

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 Paranthesis
)Right Paranthesis
:Colon
'Apostrophe
^Caret
<Less Than Sign
>Greater Than Sign
=Equal Sign
{Left Curly Bracket
}Right Curly Bracket
!Exclamation Mark
~Tilde
&Ampersand
Space
$Dollar Sign

Table of Contents