TEXT()

Format as text

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, or Time) to be formatted. See Two-argument form notes below. 
  • 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

CONCATENATE()

DATE()

DATETIME()

DECIMAL()

NUMBER()

TIME()

 

Was this helpful?

How can we improve it?

Need more help?

Try these next steps:

true
Search
Clear search
Close search
Google apps
Main menu
1552896251327592382
true
Search Help Center
true
true
true
false
false