Text

Description of formula:
Converts given parameter to a text. You can specify a number, date or logical value.
Syntax:
text(value)
or
text(value, format)
Parameters:
value
(type: Number / Date / Logical Value) The value to be converted
format
(type: Text)(optional parameter) Pattern, which will be used in conversion (only refers to date and numeric values)
 
Parameter format for numeric values:
For numeric values you can set format in the same way as in displaye format field of number type cell. For example, 0.00 in format parameter, will round to two decimal places numeric value from first parameter. More possible formats, you can find here.
 
Parameter format for dates and dates with time:
Date and time you can convert using formats of specific elements. Below there are written all possible formats:
1. d - represents the day of the month as a number from 1 through 31
2. dd - represents the day of the month as a number from 01 through 31.
3. ddd - the abbreviated name of the day (Mon, Tues, Wed, etc)
4. dddd - the full name of the day (Monday, Tuesday, etc)
5. h - 12-hour clock hour (e.g. 4, 11)
6. hh - 12-hour clock, with a leading 0 (e.g. 04, 11)
7. H - 24-hour clock hour (e.g. 4, 22)
8. HH - 24-hour clock hour, with a leading 0 (e.g. 04, 22)
9. m - minutes (e.g. 1, 58)
10. mm - minutes with a leading 0 (e.g. 01, 58)
11. M - month number (e.g. 1, 11)
12. MM - month number with leading 0 (e.g. 01, 11)
13. MMM - abbreviated month name (e.g. Sep, Oct, Nov)
14. MMMM - full month name (e.g. September, October, November)
15. s - seconds (e.g. 1, 55)
16. ss - seconds with leading 0 (e.g. 01, 55)
17. t - abbreviated AM / PM (e.g. A, P)
18. tt - AM or PM (e.g. AM, PM)
19. y or yy - two last digits of year (e.g. 2015 -> 15)
20. yyy or yyyy - full year (e.g. 2015)
21. z - time zone from Coordinated Universal Time (UTC), measured in hours (e.g. +6, -11)
22. zz - time zone from Coordinated Universal Time (UTC), measured in hours with leading 0 (e.g. +06, -11)
23. zzz - time zone from UTC, measured in hours and minutes (e.g. +06:00, -11:00)
24. multiple f - represents the most significant digits of the seconds' fraction, number of f is the number of decimal places (e.g. seconds: 0.1234 -> fff: 123) 
 
Thanks to this you can create many different data formats for example, when in format parameter there is: 'dddd, dd-MM-yyyy H:mm tt' formula can return for example:
Sunday, 17-03-2015 1:01 AM.
Returned type:
Text
Examples:
=text(1.2345, '0.00') will return 1.23
=text(now(), 'dddd, dd MMMM yyyy') will return Friday, 29 May 2015
=text(true)
=text(B3)

 

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.
Powered by Zendesk