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.
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 number 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 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: 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 maybe how to use formatting codes to achieve the format that you desire. Each example in the table is explained in detail following the table.
TEXT Function Formatting Code Examples
Format for US Dollars TEXT Function 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.
Date Format TEXT Function 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”)
Day of the Week TEXT Function Example
You may need to convert a date into the day of the week for a large number of dates. The text function comes in handy for this operation as it will take the reference of a specific date and convert it into the corresponding day of the week. =TEXT(F7,“DDDD”)
Decimal to Fraction TEXT Function 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,"#?/?")
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. Created by Joshua Crowder 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. 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. Created by Joshua Crowder © 2021 Joshua Crowder
