TEXT Function
Using the TEXT() function, you can change how a number or date is displayed by applying formatting.
Number formatting
The TEXT function formats numbers according to a given format. For example, if column A contains the number 23.7825 and you need to format it to three decimal places, you would use
TEXT(A;".3f")
As a result, you'd get the string "23.782".
Format:
TEXT(number;string_format)
String format:
The string format is written as follows: [flags][width][.precision]conversion
- [flags] — special symbols for formatting. For example, the "+" flag means that the numeric value must include the + symbol; the "-" flag means the result should be left-justified; and the "," flag sets the thousands separator for integers. It is not mandatory.
- [width] — A positive decimal integer that defines the minimum number of characters to be displayed. It is not mandatory.
- [.precision] — non-negative integer with a decimal point before it. Usually used to limit the number of characters. It is not mandatory.
- conversion — a symbol that indicates how the number should be formatted. You can use d for whole numbers, f for floats. This is mandatory.
As an example, let's say we have the number 12.34 in a field called "Number":
- TEXT({{Task.Number field}};"d") - result: 12
- TEXT({{Task.Number field}};"05d") - result: 00012
- TEXT({{Task.Number Field}};"04.3f") - result: 0012.340
- TEXT({{Task.Number field}};".4f") - result: 12.3400
Examples:
TEXT(A;".3f")
TEXT({{Task.Number field}};".3f")
TEXT(1234567; ",.2f") - result: 12 345,67
Formatting dates
Here's a table of formulas for date formatting.Let's look at an example of formatting the date 10/12/2016.
| What to display | How | Format | Formula | Result |
| Months | 1-12 | "M" | ТЕКСТ(A;"M") | 10 |
| Months | 01-12 | "MM" | TEXT(A;"MM") | 10 |
| Months | jan-dec | "MMМ" | TEXT(A;"MMM") | oct |
| Months | January-December | "MMМM" | TEXT(A;"MMMМ") | October |
| Months | J-D | "MMМMM" | TEXT(A;"MMMМM") | О |
| Days | 1-31 | "D" | TEXT(A;"D") | 12 |
| Days | 01-31 | "DD" | TEXT(A;"DD") | 12 |
| Days | Mon-Sun | "DDD" | TEXT(A;"DDD") | Wed |
| Days | monday-sunday | "DDDD" | TEXT(A;"DDDD") | wednesday |
| Years | 00-99 | "YY" | TEXT(A;"YY") | 16 |
| YEARS | 1900-9999 | "YYYY" | TEXT(A;"YYYY") | 2016 |