Text
The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. 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. For example, suppose field "Total" contains the number 23.5. To format the number as a dollar amount, you can use the following formula:
=TEXT([Total],"$0.00")
In this example, GoFormz displays $23.50.
=TEXT([Total],"$0.00") & " per hour"
GoFormz displays $23.50 per hour.
Syntax
TEXT(value, formatString)
The TEXT function syntax has the following arguments:
- value (Required) A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
- formatString (Required) A numeric format as a text string enclosed in quotation marks, for example "m/d/yyyy" or "#,##0.00". See the following sections for specific formatting guidelines.
- Guidelines for number formats
- Display decimal places and significant digits To format fractions or numbers that contain decimal points, include the following digit placeholders, decimal points, and thousand separators in the formatString argument.
- If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47
To display | As | Use this format |
---|---|---|
1234.59 | 1234.5 | ####.# |
8.9 | 8.900 | #.000 |
0.631 | 0.6 | 0.# |
12 | 12.0 | #.0# |
- Display a thousands separator: To display a comma as a thousands separator or to scale a number by a multiple of 1,000, include the following separator in the number format.
Placeholder | Description |
---|---|
, (comma) | Displays the thousands separator in a number. GoFormz separates thousands by commas if the format contains a comma that is enclosed by number signs (#) or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format_text argument is "#,###.0,", GoFormz displays the number 12200 as 12,200.0. |
To display | As | Use this format |
---|---|---|
12000 | 12,000 | #,### |
12000 | 12,000.00 | #,###.00 |
m | Displays the month as a number without a leading zero. |
mm | Displays the month as a number with a leading zero when appropriate. |
mmm | Displays the month as an abbreviation (Jan to Dec). |
mmmm | Displays the month as a full name (January to December). |
mmmmm | Displays the month as a single letter (J to D). |
d | Displays the day as a number without a leading zero. |
dd | Displays the day as a number with a leading zero when appropriate. |
ddd | Displays the day as an abbreviation (Sun to Sat). |
dddd | Displays the day as a full name (Sunday to Saturday). |
yy | Displays the year as a two-digit number. |
yyyy | Displays the year as a four-digit number. |
To display | As | Use this format |
---|---|---|
Months | 1-12 | m |
Months | 01-12 | mm |
Months | Jan-Dec | mmm |
Months | January-December | mmmm |
Months | J-D | mmmmm |
Days | 1-31 | d |
Days | 01-31 | dd |
Days | Sun-Sat | ddd |
Days | Sunday-Saturday | dddd |
Years | 00-99 | yy |
Years | 1900-1999 | yyyy |
h | Displays the hour as a number without a leading zero. |
[h] | Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss. |
hh | Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is shown based on the 12-hour clock. Otherwise, the hour is shown based on the 24-hour clock. |
m | Displays the minute as a number without a leading zero. NOTE: The m or the mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, GoFormz displays the month instead of minutes. |
[m] | Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss. |
mm | Displays the minute as a number with a leading zero when appropriate. NOTE: The m or the mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes. |
s | Displays the second as a number without a leading zero. |
[s] | Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss]. |
ss | Displays the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00. |
AM/PM | Displays the hour based on a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight. |
To display | As | Use this format |
---|---|---|
Hours | 0-23 | h |
Hours | 00-23 | hh |
Minutes | 0-59 | m |
Minutes | 00-59 | mm |
Seconds | 0-59 | s |
Seconds | 00-59 | ss |
Time | 4 AM | h AM/PM |
Time | 4:36 AM | h:mm AM/PM |
Time | 4:36:03 P | h:mm:ss A/P |
Time | 4:36:03.75 | h:mm:ss.00 |
Elapsed time (hours and minutes) | 1:02 | [h]:mm |
Elapsed time (minutes and seconds) | 62:16 | [mm]:ss |
Elapsed time (seconds and hundredths) | 3735.80 | [ss].00 |
Updated almost 2 years ago