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("2019-11-01 13:34") returns 11/1/2019 1:34:00 PM (DateTime)

TEXT([price]) returns $0.41 (Price)

TEXT([percent]) ​​​​​ returns 41% (Percent)

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) 

TEXT(when, format)

  • value - Any value to format. In the one-argument form of this function, the result format is determined by the value's type and its column's type details. See Notes below.
  • when - The temporal value (Date, DateTime, or Time) to be formatted.
  • format - The format to be applied to the temporal value (when). See Notes for recognized format characters.

Notes

This function may be used to format a value of any type as Text.

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(when, format)

The two-argument form converts a single Date, DateTime, or Time value in first argument (when) 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?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
false