The TEXT Function lets you take a numeric value and convert its format using special symbols. It is like using the Number tab on the Format cells dialog box in Excel. It is at its best when taking a date serial number and returning the month, day and year name.

The TEXT function is a computer program that you run from a worksheet cell formula. It lets you take a numeric value and convert its format using special symbols. It is like using the Number tab on the Format cells dialog box in Excel. You run the TEXT function by typing its name in a formula then followed by the information it is suppose use. The TEXT worksheet function is generally used to change values to date formats, number currency, time...etc. Many times it is used in combination with other functions as a nested function. The functions that nest this function are generally the ones that use specific text formats in their argument list like the DATEVALUE function or the LOOKUP functions. For example, say you had worksheet values displayed as dates but you wanted to generate the name of the month for the dates. Then you could use the TEXT function to accomplish this task. The TEXT function also plays an integral part in data mining and cleaning.

Whenever you type a formula in a worksheet cell, this is called syntax or grammar. The general TEXT function syntax has a format like this when you type it in a worksheet cell:

= TEXT(value, format_text)

Where value, format_text are called the function argument list. Remember, you are running a computer program at this point so the program needs information to operate and that is why there is an argument list. When you see an argument list and you see square brackets [ ] around the argument name, this means the argument is optional and you do not have to include it when typing unless you need it. So for the syntax above, you need to include two arguments for the TEXT function when typing it in a worksheet cell formula in order for it to calculate correctly. What argument values can be used are discussed below. Remember functions expect certain things in their argument lists, if you do not put the correct information in the list they will generate an error when run.

- value: A numeric value, a formula that generates a numeric value or a cell or range reference to the desired values to convert.
- format_text: Specifies the format to convert the value argument to. The specification is enclosed in quotation marks, for example "m/d/yyyy" or "#,##0.00". See the sections below for specific formatting guidelines.

To format numbers or fractions that contain decimal points, use the following digit placeholders, decimal points, and thousand separators for the format_text argument.

Character | Explanation |

0 (zero) | Displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00. |

# | Follows the same rules as the 0 (zero). However, Excel does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9is displayed. |

? | Follows the same rules as the 0 (zero). However, Excel adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0?aligns the decimal points for the numbers 8.9 and 88.99 in a column. |

(period) | Displays the decimal point in a number. |

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.

Original | Modified | Format |

1234.59 | 1234.6 | "####.#" |

8.9 | 8.900 | "#.000" |

0.631 | 0.6 | "0.#" |

1234.568 | 1234.57 | "#.0#" |

44.398 102.65 2.8 |
44.398 102.65 2.8 (with aligned decimals) |
"???.???" |

5.25 5.3 |
5 1/4 5 3/10 (with aligned fractions) |
"# ???/???" |

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.

Character | Explanation |

, (comma) | Displays the thousands separator in a number. Excel 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,", Excel displays the number 12,200,000 as 12,200.0. |

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.

Original | Modified | Format |

12000 | 1234.6 | "#,###" |

12000 | 8.900 | "#," |

12200000 | 0.6 | "0.0,," |

Display days, months, and years To display numbers as date formats (such as days, months, and years), use the following codes in the format_text argument.

Character | Explanation |

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 an abbreviation (Jan to Dec). |

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. |

Original | Modified | 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–9999 | "yyyy" |

Display hours, minutes, and seconds To display time formats (such as hours, minutes, and seconds), use the following codes in the format_text argument.

Character | Explanation |

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 that 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. |

[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 that 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, am/pm, A/P, a/p | 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. |

Original | Modified | 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 PM | "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" |

Include currency symbols To precede a number with a dollar sign ($), type the dollar sign at the beginning of the format_text argument (for example, "$#,##0.00"). To enter one of the following currency symbols in a number format, press NUM LOCK and use the numeric keypad to type the ANSI code for the symbol.

Character | Explanation |

¢ | ALT+0162 |

£ | ALT+0163 |

¥ | ALT+0165 |

Note that you must use the numeric keypad; using the ALT key with the number keys in the top row of the keyboard will not generate ANSI codes.

To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% — include the percent sign (%) in the format_text argument.

To display numbers in scientific (exponential) format, use the following exponent codes in the format_text argument.

E (E-, E+, e-, e+) Displays a number in scientific (exponential) format. Excel displays a number to the right of the "E" or "e" that corresponds to the number of places that the decimal point was moved. For example, if the format_text argument is "0.00E+00", Excel displays the number 12,200,000 as 1.22E+07. If you change the format_text argument to "#0.0E+0", Excel displays 12.2E+6.

If you include any of the following characters in the format_text argument, they are displayed exactly as entered.

Character | Explanation |

$ | Dollar sign |

+ | Plus sign |

( | Left parenthesis |

: | Colon |

^ | Caret |

' | Apostrophe |

{ | Left curly bracket |

< | Less-than sign |

= | Equal sign |

- | Minus sign |

/ | Slash mark |

) | Right parenthesis |

! | Exclamation point |

& | Ampersand |

~ | Tilde |

} | Right curly bracket |

> | Greater-than sign |

Space character |

When typing the TEXT function in a worksheet cell formula, you need to replace the argument list with arguments separating each one with a comma (arg1,arg2...). Some typical arguments you can use are:

Argument Type | Cell Formula | Example Explanation |

Cell Reference | = Text( A1, "mmm" ) | TEXT returns an abbreviated month name from cell A1 like Jun |

Range Reference | = Text( A1:A10, "#,###.#" ) | TEXT returns text values formatted as 1,234.00 from the range A1 to A10 and returns an array |

Cell and Range Names | = TEXT( Current_Date, "h:mm AM/PM" ) | TEXT returns the time like 4:33 PM from the cell name Current_Date * |

* It is possible to name a cell or group of cells on a worksheet and use that name in place of a range reference or cell reference. Consult Excel help on how to name a cell.

- The format_text argument cannot contain an asterisk (*).
- Using the TEXT function converts a numeric value to formatted text, and the result can no longer be calculated as a number. To format a cell so that its value remains numeric, right-click the cell, click Format Cells, and then in the Format Cells dialog box, on the Number tab, set the formatting options you want. For more information about using the Format Cells dialog box, click the Help button (?) in the upper right corner of the dialog box.

Need Help? Please call us at 1.805.498.7162

Excel Training Services

Microsoft Excel Products

EMAGENIT Company Information

Copyright © 2002-2019

EMAGENIT All Rights Reserved