Excel TEXT Function - Microsoft Excel TEXT Function Tutorial

Excel TEXT Function - Microsoft Excel TEXT Function Tutorial

Excel TEXT Function - Microsoft Excel TEXT Function Tutorial

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.

What is the Microsoft Excel TEXT Function?

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.


How Do You Type the TEXT Worksheet Function in a Formula?

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.


TEXT Worksheet Function Argument Definitions


TEXT Function Formats

Guidelines for Number Formats

Display Decimal Places and Significant Digits

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)
"# ???/???"

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.

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,,"

Guidelines for Date and Time formats

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"

Guidelines for Currency, Percentages, and Scientific Notation Format

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.

Display Percentages

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.

Display Scientific Notations

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.

Guidelines for Including Text and Adding Spacing

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

What Do I Type for a TEXT Function Argument?

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.

Things to Know About the Text Function

Next Excel Training Dates - Public Training and Live Online Courses

Our next public Excel training workshops and live online Excel courses are listed below. For all of the dates and times, please visit our Training Schedule page using the link above in the main menu.

Business, Engineering and Science
Beginning Excel Training for Business Professionals Intermediate Excel Training for Business Professionals Advanced Excel Training for Business Professionals Beginning Excel VBA Training for Business Professionals Intermediate Excel VBA Training for Business Professionals Advanced Excel VBA Training for Business Professionals Advanced Excel Training for Engineers / Scientists Beginning Excel VBA Training for Engineers / Scientists Intermediate Excel VBA Training for Engineers / Scientists Advanced Excel VBA Training for Engineers / Scientists Microsoft Excel Dashboards Training for Combined Audience
Beginning Excel Training for Business
Next Date:
See Schedule
Intermediate Excel Training for Business
Next Date:
See Schedule
Advanced Excel Training for Business
Next Date:
See Schedule
Beginning Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Intermediate Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Advanced Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Advanced Excel Training for Engineers / Scientists
Next Date:
See Schedule
Beginning Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Intermediate Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Advanced Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Microsoft Excel Dashboard Training
Next Date:
See Schedule

Public Training Location

  • Santa Barbara, CA
  • Los Angeles, CA
  • Pasadena, CA
  • Reseda, CA
  • Chatsworth, CA
  • Topanga, CA
  • Santa Clarita, CA
  • Newbury Park, CA
  • Camarillo, CA
  • Calabasas, CA
  • Valencia, CA
  • West Hollywood, CA
  • Goleta, CA
  • Palmdale, CA
  • San Fernando, CA
  • Burbank, CA
  • La Canada, CA
  • Altadena, CA
  • Culver City, CA
  • Rosemead, CA
  • Anaheim, CA
  • El Segundo, CA
  • Fillmore, CA
  • Gardena, CA
  • Encino, CA
  • Oxnard, CA
  • Mojave, CA
  • La Crescenta, CA
  • Glendale, CA
  • Flintridge, CA
  • Beverly Hills
  • Arcadia, CA
  • Inglewood, CA
  • Hawthorne, CA
  • Manhattan Beach, CA
  • Santa Paula, CA
  • Century City, CA
  • Sherman Oaks, CA
  • Reseda, CA
  • Woodland Hills, CA
  • Hollywood, CA
  • Studio City, CA
  • Fillmore, CA
  • Ventura, CA
  • Moorpark, CA
  • Simi Valley, CA
  • Santa Monica, CA
  • Encino, CA

EMAGENIT performs Excel onsite company training in the cities listed above as well as the entire Northern and Southern California areas, United States and Canada.

LinkedIn
Facebook
Information
Need Help? Please call us at 1.866.924.6244

Copyright © 2002-2017

EMAGENIT All Rights Reserved