Related Functions:

The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings.

Usage note: This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.

Syntax

=TEXT(value,format_text)

Arguments

Argument Description
value A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value
format_text A text string that defines the formatting that you want to be applied to the supplied value. The most used format definitions are:

  0 (zero) Forces the display of a digit in its place
  # Display digit if it adds to the accuracy of the number, but don’t display if a leading zero or a zero at the end of a decimal
  . (period) Displays the decimal point in a number
  , (comma) Displays the thousands separator in a number
  d Day of the month or day of the week

d = one or two digit representation (e.g. 1, 12)
dd = 2 digit representation (e.g. 01, 12)
ddd = abbreviated day of week (e.g. Mon, Tue)
dddd = full name of day of week (e.g. Monday, Tuesday)
  m Month, when used as part of a date

m = one or two digit representation (e.g. 1, 12)
mm = 2 digit representation (e.g. 01, 12)
mmm = abbreviated month name (e.g. Jan, Dec)
mmmm = full name of month (e.g. January, December)
  y Year

yy = 2 digit representation of year (e.g. 09, 14)
yyyy = 4 digit representation of year (e.g. 2009, 2014)
  h Hour

h = one or two digit representation (e.g. 1, 24)
hh = 2 digit representation (e.g. 01, 24)
[h] = Displays elapsed time in hours, e.g. a formula returns a time in which the number of hours exceeds 24 ([h]:mm:ss)
  m Minute

m = one or two digit representation (e.g. 1, 59)
mm = 2 digit representation (e.g. 01, 59)
[mm] = Displays elapsed time in minutes, e.g. a formula returns a time in which the number of minutes exceeds 60 ([mm]:ss)
  s Second

s = one or two digit representation (e.g. 1, 59)
ss = 2 digit representation (e.g. 01, 59)
[ss] = Displays elapsed time in seconds, e.g. a formula that returns a time in which the number of seconds exceeds 60 ([ss])
  AM/PM, 
am/pm, 
A/P, a/p
Displays the hour based on a 12-hour clock, e.g. AMamA, or a for times from midnight until noon and PMpmP, or p for times from noon until midnight

Examples

  A B C
1 Value Formula Result
2 09/09/2014 =TEXT(A2,”dd/mm/yyyy”) 09/09/2014
3 41891 =TEXT(A3,”dd/mm/yyyy”) 09/09/2014
4 41891 =TEXT(A4,”dd-mmm-yyyy”) 09-Sep-2014
5 0.75 =TEXT(A5,”hh:mm”) 18:00
6 36.363636 =TEXT(A6,”0.00″) 36.363636
7 .4587 =TEXT(A7,”0.0%”) 45.9%
8 794.8 =TEXT(A8,”$#,##0.00″) $794.80
9 -7 =TEXT(A9,”+$#,##0.00;-$#,##0.00;$0.00″) -$7.00
10 7 =TEXT(A10,”+$#,##0.00;-$#,##0.00;$0.00″) $7.00
11 0 =TEXT(A11,”+$#,##0.00;-$#,##0.00;$0.00″) $0.00

Usage note: TEXT is especially useful when you want to embed the numeric output of a formula or function and present it in a particular format inside other text. For example, “Sales last year increased by over $28,500”, where the number 28500 has been formatted with a currency symbol and thousands separator.

  • Note the results of the TEXT function, in column C of the spreadsheet above, are all text values, rather than numeric values.