Skip to main content

TEXT

The TEXT function converts a value into a string of a given format.

This function can be useful when you want to display numbers or dates in a specific format, for example, with a fixed number of decimal places or in a specific date format.

Syntax​

TEXT(value, format)

ArgumentDescriptionPermitted values
valueNumber, date, or time that needs to be convertedNumber, date, or time
formatFormatting template enclosed in quotation marksSee the List of formats and examples of use subsection below.

List of formats and examples of use​

Number formats​

  • “0”: Displays the number without decimal places. For example, =TEXT(1234, “0”) will return “1234”.
  • “0.00”: Displays a number with two decimal places. For example, =TEXT(1234.5, "0.00") will return “1234.50”.
  • “#”: Displays only significant digits. For example, =TEXT(1234.05, "#") returns "1234", while =TEXT(0, "#") returns "" (an empty string).
  • “# ##0”: Displays the number with thousands separators. For example, =TEXT(1234567; “# ##0”) will return "1 234 567".

Date formats​

  • “dd.mm.yyyy”: Displays the date in "day.month.year" format. For example, =TEXT(DATE(2023, 10, 5), "dd.mm.yyyy") will return "05.10.2023".
  • “mmm dd, yyyy”: Displays the date in the format “month day, year”. For example, =TEXT(DATE(2023, 10, 5), "mmm dd, yyyy") will return "Oct 05, 2023".
  • "dd/mm/yyyy": Displays the date in "day/month/year" format. For example, =TEXT(DATE(2023, 10, 5), "dd/mm/yyyy") will return “05/10/2023”.

Time formats​

  • "hh:mm:ss": Displays the time in "hours:minutes:seconds" format. For example, =TEXT(NOW(), "hh:mm:ss") will return the time from the current date.
  • “hh:mm”: Displays the time in "hours:minutes" format. For example, =TEXT(NOW(), "hh:mm") will return the time from the current date.

Currency formats​

  • "$# ##0.00" : Displays the number as currency with two decimal places. For example, =TEXT(1234.5, "$# ##0.00") will return “$1 234.50”.
  • "€ # ##0" : Displays the number with the euro symbol. For example, =TEXT(1234, "€ # ##0") will return "€ 1 234".

Notes​

  • The text format must be enclosed in quotation marks.
  • The TEXT function returns a string, so the result cannot be used in further numerical calculations without converting it back to a number.