DriveWorks Pro 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.00%"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,000"
TEXT(76500, "0,0.0")"76,500.0"

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"
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.

Use the result of a Date Picker Control directly in the function

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.

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.

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%"

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.

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

DriveWorks Tech Tips Portal

Date Picker

This project shows how to use the date functionality within DriveWorks, taking things such as leap years into account.

View all Tech Tips

DriveWorks Tech Tips Portal is available to DriveWorks Pro customers with an active subscription and support contract.

Tech Tips provide cut down projects that highlight specific functionality for faster and more effective learning.

To access the portal:

  1. Go to https://hub.driveworks.co.uk/tech-tips/
  2. Login with your DriveWorks Pro account credentials, if asked
  3. Click the Visit Tech Tips Portal button for access

The portal provides a search facility, start typing the name of the Tech Tip (as indicated above) to display the Tip you require.

Table of Contents