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)

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.