TEXT

Description

Converts the specified number into text, based on the specified format. Example: Worksheets normally stores dates using their serial numbers for efficiency in calculations; you can use the TEXT function to reformat the date as a user-readable value.

Syntax

TEXT(

value

format_text

)

value

: The number to convert.

format_text

: The format to use when converting the number.

Format options:

0	Display a digit.
#	Display a digit if it increases number accuracy. Don't display a digit if it is a leading zero or a zero at the end of a decimal.
.	Specifies the position of the decimal.
d	Day of month or day of week: d = 1 or 2 digit representation (e.g. 1, 12) dd = 2 digit representation (such as 01, 12) ddd = day of week abbreviation (such as Mon, Tue) dddd = full day of week (such as Monday, Tuesday)
m	Month (when part of a date): m = 1 or 2 digit representation (such as 1, 12) mm = 2 digit representation (such as 01, 12) mmm = month name abbreviation (such as Jan, Dec) mmmm = full month name (such as January, December)
y	Year: yy = 2 digit representation (such as 17, 09) yyyy = 4 digit representation (such as 2017)
h	Hour: h = 1 or 2 digit representation (such as 1, 20) hh = 2 digit representation (such as 01, 20)
m	Minute (when part of a time): m = 1 or 2 digit representation (such as 1, 55) mm = 2 digit representation (such as 01, 55)
s	Second: s = 1 or two 2 digit representation (such as 1, 45) ss = 2 digit representation (such as 01, 45)
AM/PM	Represent time using a 12-hour clock, followed by "AM" or "PM".

Example

Formula	Result
=TEXT(A1, "mm/dd/yyyy") Where cell A1 contains 43210 .	04/20/2018
=TEXT(A1, "dd/mm/yyyy") Where cell A1 contains 12/25/2017 .	25/12/2017
=TEXT(A1, "0.0%") Where cell A1 contains 123.45678 .	12345.7%