text() Function

The text() function allows you to format Number, Date, Time, or Date and time values as you convert them into text strings.

Syntax

text( value, format )

value: (Number/Date/Time/Datetime) The value to convert to a text string.

format: (Text) The output format string, supporting "date/time" format, "positive" format, "positive;negative" format or "positive;negative;zero" format.

Returns

Text

Notes

The format "date/time" can only be for the positive.

Beans are not supported in the value parameter.

When you specify a numeric text format for a decimal number, such as "$000.00" or "$###.##", any values that go beyond the number of decimal places in your format pattern are dropped, without rounding.

Examples

text(10.25, "$00.00") returns $10.25

text(-10.25, "$00.00;#00.00") returns #10.25

text(0, "$00.00;#00.00;*00.0") returns *00.0

text(38353, "mmm/dd/yyyy") returns Jan/04/2140

text(now(),"mmmm") where the current date and time is May 30, 2006 12:00 PM, returns May

Possible text() Function Date/Time Formats

The text(value,"format") function allows you to format the text output in various ways. The following table describes available date, time, and datetime formats you can specify.

Format Output
mmmmm J F M A M J J A S O N D
mmmm January February March April May June July August September October November December
mmm Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
mm 01 .. 12
m 1 .. 12
MMMMM J F M A M J J A S O N D
MMMM January February March April May June July August September October November December
MMM Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
MM 01 .. 12
M 1 .. 12
yyyy 1972
yy 72
ddd 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10th 11th 12th 13th 14th 15th 16th 17th 18th 19th 20th 21st 22nd 23rd 24th 25th 26th 27th 28th 29th 30th 31st
dd 01 .. 31
d 1 .. 31
hh 01..12 (if AM/PM) or 00..23 (if no AM/PM)
h 1..12 (if AM/PM) or 0..23 (if no AM/PM)
HH 01..12 (if AM/PM) or 00..23 (if no AM/PM)
H 1..12 (if AM/PM) or 0..23 (if no AM/PM)
kk 01..12 (if AM/PM) or 01..24 (if no AM/PM)
k 1..12 (if AM/PM) or 1..24 (if no AM/PM)
mm minute 00..59 (If hour already processed, i.e., h to left anywhere in format)
ss second 00..59
AM/PM AM/PM in uppercase
am/pm am/pm in lowercase
A/P A/P in uppercase
a/p a/p in lowercase
a AM or PM in uppercase
aa AM or PM in uppercase
[h] the number of hours in the era
@ 26310.5426 (the serial date value)
dddd Saturday, Sunday, Monday, etc.
EEEE Saturday, Sunday, Monday, etc.
EEE Sat, Sun, Mon, etc.
z Timezone Name short form (EST)
zzzz Timezone Name long form (Eastern Standard Time)

Possible Number Formats for text() Function Output

If the format you use contains the number sign (#) zero (0) or a question mark (?), then the output takes a number format. The output is then treated as a number rather than as a date/time.

You can split the number format into positive;negative;zero;text formats (where each optional format is separated by a semi-colon [;]).

Normally, positive numbers do not show a sign symbol. To use plus (+) or minus (-) symbols, specify the positive and negative number-formats separately (as in the following examples).

text(-3.434,"+0000.###;-0000.###")returns -0003.434

text(3.434,"+0000.###;-0000.###") returns +0003.434

The following table lists the characters that can be used to the left of any decimal for defining a number format.

Format Meaning
0 Numeric digit or leading 0
# Numeric digit or leading space
, Grouping seperator
. Decimal point. Switches to right of decimal formatting
- Always "-"
+ "+" if positive, "-" if negative
c The character c
$ The currency character
% Any % multiplies the number by 100
Any other character Represented as itself

The following table lists the characters that can be used to the right of any decimal for defining a number format.

Format Meaning
0 Numeric digit or leading 0
# Numeric digit or leading space
, Grouping seperator
c The character c
$ The currency character
Any other character Represented as itself

Criteria Formatting for Numbers

In these two examples, a positive and a negative number format (with red) are defined for the expression. The negative format and red color are only applied when the value is less than 0.

text("-100000", "###,###.##;[<0][red] - ###,###.##") returns <font color="#FF0000"> - 100,000.00</font>

text("100000", "###,###.##;[<0][red] - ###,###.##") returns 100,000.00

Different formats can be applied using the criteria listed in the following table.

Format Description
positive_format;negative_format;zero_format All specified
positive_format;negative_format Zero is same as positive
positive_format Negative is "-positive", Zero is positive
[>number]format;… Use when value > number
[>=number]format;… Use when value >= number
[ Use when value < number
[<=number]format;… Use when value <= number
[=number]format;… Use when value = number
[<>number]format;… Use when value <> number

Other Sample Criteria

Format Meaning
[<=9999999]000-0000;(000) 000-0000 US phone number
000-00-0000 US Social Security Number
[<=99999]00000;00000-0000 US Zip or Zip+4 Code
FEEDBACK