Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
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:
- A cell needs clicked and =TEXT( needs to be typed in the cell.
- After the open parenthesis, type the value to be formatted (or reference that value by clicking in a cell) followed by a comma.
- Next, add the format code to be used in quotations.
- 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
A Format for US Dollars
A Date Format
Day of the Week
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.
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.
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.
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.
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.
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.
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.
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.
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.
- Converting Measurement Units: Using the Convert Function in Microsoft Excel
Describes how to use the CONVERT function within Microsoft Excel to convert various types of measurement units to other types of units. Types of conversions include: weight and mass, distance, time, pressure, force, energy , power, magnetism, tempera
- How to Use the VLOOKUP Function in Excel
Covers how to use the VLOOKUP function in Microsoft Excel with examples on the correct syntax to use.
- How to Remove Errors in Excel With the IFERROR Function
Shows a Microsoft Excel user how to apply the IFERROR Function to remove errors in cells.
- How to Use the ABS Function in Excel
Covers how to use the ABS function in Microsoft Excel with examples on the correct syntax to use.
- How to Use the AVERAGE Function in Excel
Shows how to use the AVERAGE function in Excel with application examples.
- How to Use the COUNT Function in Excel
Shows a Microsoft Excel user how to use the count function in a spreadsheet.
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