Updated date:

How to Use the TEXT Function in Excel

Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.

The TEXT function is shown above where it takes today's date represented by the TODAY function and displays Monday as the new format.

The TEXT function is shown above where it takes today's date represented by the TODAY function and displays Monday as the new format.

Here, we discuss using the TEXT function in Excel. You'll see how to enter the function manually as well as how to insert it. I'll cover four common examples that represent situations where this function is commonly used.

Additional research or practice may be required if you plan to use this function to complete complex formatting in your spreadsheets. I'll show you where to find these formatting codes at the end of this article.

The Purpose of the TEXT Function

The TEXT function allows one to change the appearance of a value (number or text) with formatting options in an Excel worksheet. There is an abundant amount of applications for this function. For example, it allows you to format numbers as dates, dollar amounts, percentages, decimals, in addition to fulfilling custom formatting needs. Custom formatting with this function allows you to add text, symbols, and numbers to the value of your choice.

The TEXT Function Syntax

When using the TEXT function, it's important to understand that it needs to be inputted like a formula to work properly. To manually add this formula to a cell, the following steps need to be taken:

  1. A cell needs clicked and =TEXT( needs to be typed in the cell.
  2. After the open parenthesis, type the value to be formatted (or reference that value by clicking in a cell) followed by a comma.
  3. Next, add the format code to be used in quotations.
  4. Add a close parenthesis and enter the data.

The syntax of the TEXT function can be found below in bold text. The value and format code are described in greater detail in the following sections.

=TEXT(Value you want to format, "Format code you want to apply")

The Value and Formatting Codes

The value that can be manipulated may be a general number, a date, time, or even a number with letters and symbols combined with it. This value that you desire to format can be formatted in a very specific way.

Excel has an abundance of formatting codes which allow for a lot of flexibility with this function. Some of the more popular codes are displayed in the table below. The tricky part of using this function may be how to use formatting codes to achieve the format that you desire.

Each example in the table is explained in detail following the table.

Formatting Code Examples

DisplayFormat

A Format for US Dollars

$#,##0.00

A Date Format

MM/DD/YY

Day of the Week

DDDD

Turns a Decimal to Fraction

# ?/?

Format for US Dollars Example

See the example in the illustration below that uses the first formatting code from the table. The code for this operation is $#,##0.00 and may seem to have an odd appearance. The code here is very specific, and in this case, each character has a very special purpose. Suppose you have an amount appearing as 35154 that should represent a dollar amount and must be reformatted.

To use this number and code in a formula, I used the cell reference for the amount, which was followed by the formatting code. The formula can be seen below in bold or in the next illustration.

=TEXT(F7,"$#,##0.00")

Notice that the formatting code uses the symbol # to represent whole numbers. Zeros are also added to represent numbers to the right of the decimal place or to serve as placeholders when numbers past the decimal place do not exist.

how-to-use-the-text-function-in-excel

Date Format Example

Preferred date formats can come in a variety. This is especially true if you need to cater to an international audience. For instance, see the different date format that is used to change the date 8/29/2021 in the illustration with the formula in bold below.

=TEXT(F7,"MM/DD/YY")

how-to-use-the-text-function-in-excel

Day of the Week Example

You may need to convert a date into the day of the week for a large amount of dates. The text function comes in handy for this operation as it will take the reference of a date and convert it into the corresponding day of the week.

=TEXT(F7,"DDDD")

how-to-use-the-text-function-in-excel

Decimal to Fraction Example

Using this function to convert decimals to fractions can be helpful in the area of academics for teachers and students alike. Decimal conversion is represented by the code below in bold and can also be seen in the illustration below.

=TEXT(F7,"#?/?")

how-to-use-the-text-function-in-excel

Inserting the TEXT Function

The TEXT function can be entered in Excel in a few ways. Instead of typing the function out, you may insert the function with a few steps. The first task to insert the function requires you to click in the cell where you want the function results to appear, followed by a click on the formulas tab.

Next, the "Text" button on the Excel ribbon needs to be selected, followed by selecting text from the drop-down menu.

how-to-use-the-text-function-in-excel

The functional arguments window will appear where values to be formatted can be added to the "Value" field without a comma, and the corresponding format code can be entered in the "Format_test" field without quotations.

how-to-use-the-text-function-in-excel

Before clicking the OK button, you can see how your format will be displayed to allow you to correct errors.

Finding Formatting Codes

If you are unfamiliar with formatting codes, you can find examples in Excel. By clicking on the arrow in the number section while in the home tab, you will be able to navigate through different formatting topics to see which formatting may work for your situation.

The illustration show the format cells window where you can search for the codes of different formatting styles.

The illustration show the format cells window where you can search for the codes of different formatting styles.

References

Microsoft. (n.d.). TEXT function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c.

Alexander, M., Kusleika, D., & Walkenbach, J. (2019). Excel® 2019: bible. Indianapolis: Wiley.

Related Articles

To learn more about using functions in Excel I recommend purchasing Excel Formulas & Functions For Dummies.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2021 Joshua Crowder

Related Articles