TEXT(value, format_text)

Returns the number as a formatted text string.

valueThe value representing the number you want converted to text.
format_textThe format to display the result:
"d" = day as a number, no leading zero
"dd" = day as a number with a leading zero
"ddd" = day as an abbreviation (3 characters)
"dddd" = day in full
"m" = month as a number, no leading zero
"mm" = month as a number with a leading zero
"mmm" = month as an abbreviation (3 characters)
"mmmm" = month in full
"yy" = year as a two digit number
"yyyy" = year as a four digit number
"h" = hour as a number, no leading zero
"hh" = hour as a number with a leading zero
"m" = minute as a number, no leading zero
"mm" = minute as a number with a leading zero
"s" = second as a number, no leading zero
"ss" = second as a number with a leading zero

Remarks

* If "format_text" is not enclosed in speech marks, then #NAME? is returned.
* The "value" can be a numeric value, a cell reference or a formula that evaluates to a numeric value.
* The "format_text" argument must be enclosed in speech marks.
* The "format_text" cannot contain an asterisk (*).
* The "format_text" can include any valid Time Format or Custom Format.
* Formatting a cell using the (Format > Cells) dialog box only changes how the value is displayed.
* Using this function will actually change the value to text, and the actual value might have changed.
* This function can be used with other languages (for example Japanese).
* You can use the DOLLAR function to return a text string of a number with the currency formatting $0,000.00.
* You can use the FIXED function to return the text string of a number rounded to a fixed number of decimal places.
* You can use the TEXTJOIN function to return the text string that is a concatenation of several strings.
* The equivalent VBA function is FORMAT
* For the Microsoft documentation refer to support.office.com

 AB
1=TEXT("16","00.00") = 16.00200
2=TEXT(2.715,"$0.00") = $2.72=30/0 = #DIV/0!
3=TEXT(37324,"dd/mm/yy") = 09/03/2002 
4=TEXT("4/15/91","mmmm dd, yyyy") = 15/04/1991 
5=TEXT(0.2,"0%") = 20% 
6=TEXT(100/4,"0.00") = 25.00 
7=TEXT(12345.6789,"#,##,##.00") = 12345.68 
8=TEXT("01/01/1997","mm-dd-yyyy") = 01-01-1997 
9=TEXT(NOW(),"mm-dd-yyyy") = 10-15-2019 
10=TEXT(TODAY(),"dddd-yyyy") = Tuesday-2019 
11=TEXT("01/01/1997","ddd") = Wed 
12=TEXT(100,0*0) = 100 
13=TEXT(B1,"£0.00") = £200.00 
14=TEXT(B1,"General") = 200.00 
15=TEXT(B2,"0%") = #DIV/0! 


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top