Returns the formatted text from the first argument.
Sample usage
One-argument form
TEXT(4096)
returns 4,096
(Number
)
TEXT("4096")
returns 4096
(Text
)
TEXT(32768.1024)
returns 32,768.10
(Decimal
)
TEXT([price])
returns $0.41
(Price
)
TEXT([percent])
returns 41%
(Percent
)
TEXT("2019-11-01 13:34")
returns 11/1/2019 1:34:00 PM
(DateTime
)
TEXT("12.15")
returns 12/15/2023
(Date
)
Two-argument form
TEXT("2019-11-01", "dd/mm/yyyy")
returns 01/11/2019
TEXT("2019-11-01 1:34 PM", "HH:MM")
returns 13:34
TEXT("2:23", "H")
returns 2
TEXT("2:23 PM", "H")
returns 14
TEXT("14:23", "H")
returns 14
TEXT("14:23", "H A/P")
returns 2 P
TEXT("14:23", "H AM/PM")
returns 2 PM
TEXT("4/15/2020 14:23", "M")
returns 4
TEXT("4/15/2020 14:23", "H:M")
returns 14:23
Syntax
TEXT(value, [format])
value
- Value to format.- If
format
is omitted, the result format is determined by the value's type and its column's type details. See One-argument form notes below. - If
format
is specified, the temporal value (Date
,DateTime
, orTime
) to be formatted. See Two-argument form notes below.
- If
format
- Optional. The format to be applied to the temporal value (value
). See Two-argument form notes below for recognized format characters.
Notes
This function may be used to format a value of any type as Text
.
Some constant values, such as "MM/DD/YYYY"
, are evaluated as a Date
value by AppSheet. Similarly, "000:00:00"
is evaluated as a Duration
value. This doesn't mean your spreadsheet data must use the same formats: the date and time formats in your spreadsheets are determined by the locale/language setting. Column will always be evaluated as the type of column. Additionally, you can convert data, both columns and string literals, to specific types using functions such as DATE()
, TIME()
, or DATETIME()
.
There are two forms of the function: the one-argument form and the two-argument form.
One-argument form
TEXT(value)
The one-argument form converts a single value of any type (value
) using implicit formatting according to the current locale. The locale affects such things as:
- Date format
- Decimal separator
- Thousands separator
- Time format
If the argument (value
) is a column value reference (such as, [Price])
, the column's Type Details settings will also be applied to the formatted result. For instance, a Decimal
column's Decimal digits property can be used to limit the precision in the result; or a Date
column's Use long date format can be used to produce a more verbose result.
Two-argument form
TEXT(value, format)
The two-argument form converts a single Date
, DateTime
, or Time
value in first argument (value
) according to an explicit format guide in the second argument (format
).
Recognized format characters are:
YY
two-digit year (19
)YYYY
four-digit year (2019)
M
one- or two-digit month (3
,12
)MM
two-digit month (03
,12
)MMM
short name of the month (Mar
,Dec
)MMMM
long name of the month (March
,December
)D
one- or two-digit day of the month (8
,31
)DD
two-digit day of the month (08
,31
)DDD
short name of the day of the week (Wed
)DDDD
long name of the day of the week (Wednesday
)H
one- or two-digit hour (9
,10
)HH
two-digit hour (09
,10
)M
one- or two-digit minute (6
,58
)MM
two-digit minute (06
,58
)S
one- or two-digit second (0
,18
)SS
two-digit second (00
,18
)AM/PM
for "AM" or "PM"A/P
for "A" or "P"
The meaning of M
is contextual: if it comes after an H
or immediately before an S
, it means "minutes".
If one of AM/PM
or A/P
is specified, hours are displayed in 12-hour format; otherwise they are displayed in 24-hour format.
Any characters not listed above are reproduced verbatim in the output text, most commonly -
(hyphen), /
(slash), and :
(colon).
See also