Excel TEXT() Function: Format Numbers as Readable Text

The TEXT() function in Excel is a powerful tool for formatting numerical data into readable and well-presented text strings. Whether you're working with dates, currency, percentages, or custom number formats, the TEXT() function allows you to control exactly how these values appear. 

While Excel offers built-in cell formatting options, those only affect how the value is displayed in the cell. The TEXT() function is useful when you need to embed formatted values into larger text strings, such as custom messages, dynamic labels, or combined cell outputs. In this tutorial, I will show you the different applications of the Excel TEXT() function and how to troubleshoot common errors using practical examples.

If you are unfamiliar with some of the basics, I recommend you take our Introduction to Excel course to build a solid foundation in Excel. You should also check out our Data Preparation in Excel course to learn more about transforming raw data.

What the Excel TEXT() Function Does

The TEXT() function in Excel converts a numeric value into a text string that follows a specified format using Excel’s number formatting codes. Therefore, the main use of the TEXT() function is to transform raw numbers into human-friendly, formatted text, making your data easier to read and interpret.

Other common use cases of the TEXT() function include the following:

• Displaying numbers with currency symbols or comma separators
• Showing readable dates or times
• Adding leading zeros to fixed-length codes
• Creating formatted outputs like phone numbers or scientific notation

I will show you the above use cases with practical examples in the next sections.

How to Use the Excel TEXT() Function

To understand how to use the Excel TEXT() function, let's examine its syntax and examples of format code types you can apply.

Formula breakdown

The Excel TEXT() function uses two key arguments to transform a numeric input into a formatted text string:

=TEXT(value, format_text)

Where:

• value: The numeric input you want to format. It can be a number, a cell reference containing a number, a date, or a formula that returns a numeric value.

• format_text: A text string that specifies the format you want to apply, always enclosed in double quotes. 

Format code types and examples

The table below summarizes the common Excel format codes and their applications in the TEXT() function.

Opening Excel's Format Cells dialog allows you to easily find and reuse many of the format codes listed above. 

• Select a cell, then press Ctrl+1 (or Cmd+1 on Mac) to open the Format Cells window. 
• Under the Number tab, choose a category like Number, Date, Custom, or Special.
• Copy the format code shown in the Type field. Click OK to apply.

Using TEXT() with Dates and Times

Excel stores dates and times as numbers, but when you want to present them clearly in text, the TEXT() function lets you control their appearance with precision.

Using TEXT() with TODAY() and NOW()

The TEXT() function is useful for formatting dynamic date and time values returned by Excel’s TODAY() and NOW() functions. 

For example, the formula below converts the current date into a full-text format like "May 1, 2025".

=TEXT(TODAY(), "mmmm d, yyyy")

The following formula takes the time portion of the NOW() function to display the current time in 12-hour format with AM/PM.

=TEXT(NOW(), "hh:mm AM/PM")

Concatenating date/time with labels

You can also use the TEXT() function to embed formatted dates or times into a sentence or label. For example, the function below will generate the output "Report generated on: 05/01/25".

="Report generated on: " & TEXT(TODAY(), "MM/DD/YY")

Note that if you concatenate a date or time directly without the TEXT() function, Excel will display the underlying serial number rather than a readable date. 

Using TEXT() with percentages, currency, and fractions

The TEXT() function in Excel is particularly useful when formatting percentages, currency values, and fractions for everyday analysis and tasks. Here's how you can transform these different types of numerical data:

For percentages, the TEXT() function multiplies the decimal value by 100 and adds the percent symbol. For example, the formula below converts 0.275 to "27.5%" with one decimal place of precision.

=TEXT(0.275, "0.0%")

When working with currency, you can format monetary values with the appropriate symbol, thousand separators, and decimal places. For example, the following function displays "1234.56" as "$1,234.56" with the dollar sign, thousand separator, and two decimal places.

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

For fractions, the TEXT() function can convert decimal values to fraction notation. The function below displays ”4.5” as ”4 ½” , showing the value as a mixed number with both whole number and fraction components.

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

Practical Applications and Combinations

The real power of the TEXT() function shows when you combine it with text and other functions to produce polished outputs. Let us look at the following examples.

Embed values in messages

You can include formatted numbers directly inside messages using & or the CONCAT() function. For example, if you have the price in A2 as 2500, the following function will display "Final Price: $2,500.00".

="Final Price: $" & TEXT(A2, "#,##0.00")

Add leading zeros

You can also use the TEXT() function to standardize codes, IDs, or ZIP/postal codes with leading zeros. For example, if you have the code 4567 A2, the following function will display "000004567".

=TEXT(A2, "000000000")

Format phone numbers

Since phone numbers need specific formatting for improved readability, you can use the TEXT() function to transform them. For example, if A2 contains 1234567890, the formula below displays it as ”(123) 456-7890”.

=TEXT(A2, "(###) ###-####")

Advanced Examples and Tips

The TEXT() function can also be used in more complex formulas and dynamic reports to enhance clarity and interactivity. The following are some examples of its advanced applications.

Use inside IF() or IFS() formulas

You can combine TEXT() with logical functions like IF() or IFS() to produce conditional messages based on numeric values. For example, the formula below will display either "Profit: $250.00" or "Loss: $250.00" depending on the value in A2.

=IFS(A2 > 0, "Profit: " & TEXT(A2, "$0.00"), A2 < 0, "Loss: " & TEXT(ABS(A2), "$0.00"))

Use with array formulas or dynamic reports

The TEXT() function allows you to format values dynamically within array formulas or dynamic reports, such as grouping or sorting by month. For example, the formula below extracts and formats the month and day from a date, making it easier to sort or analyze birthdays or events regardless of year.

=TEXT(A2, "mm-dd")

Region-specific behavior

You should be aware that the TEXT() format codes can vary by locale. For example, the date format "dd/mm/yyyy" is standard in many regions outside the US, while "mm/dd/yyyy" is typical in the US. Using the wrong format code for your region can result in errors or unexpected results. If you're working with international teams or shared spreadsheets, always double-check how regional settings may affect your formatting results.

Troubleshooting TEXT() Function Issues

You may encounter errors when using the Excel TEXT() function. Let's explore some of these issues and how to troubleshoot them.

Common errors

The common errors with the TEXT() function include the following:

• #NAME?: This error occurs when quotation marks are missing from the format string. For example, when you write =TEXT(A1, mm/dd/yyyy) instead of =TEXT(A1, "mm/dd/yyyy").

• #VALUE!: This issue occurs when the format code is invalid, incorrectly structured, or not supported in your locale.

Best practices

When using the TEXT() function in Excel, consider the following best practices to avoid these pitfalls.

• Keep original values separate: Since TEXT() converts numbers to text, the result cannot be used in further calculations. Store original numeric values in separate cells or columns for calculations, and use TEXT() only for display or reporting purposes

• Format only when necessary: Apply the TEXT() function when you need formatted values in labels, reports, or combined strings, not for calculations or data analysis.

Other Useful Text Functions in Excel

In addition to the TEXT() function, Excel offers various text functions that help manipulate, clean, and combine textual data. The following are these functions with their application:

• VALUE(): Converts a text string that looks like a number back into a numeric value. For example, the function =VALUE("123.45") will display 123.45 as a number.

• LEFT(), RIGHT(), MID(): These functions allow you to extract portions of text strings. For example, =LEFT(A1, 4) extracts the first 4 characters and =RIGHT(A1, 3) extracts the last 3 characters. =MID(A1, 2, 5) extracts 5 characters starting from the 2nd position.

• CONCAT(), TEXTJOIN(): These functions allow you to combine text from multiple cells. CONCAT() joins two or more text strings into one, while TEXTJOIN() combines text with a specified delimiter between each item.

• TRIM(), SUBSTITUTE(), REPLACE(): These functions help clean and modify text data. The TRIM() function removes extra spaces from text while SUBSTITUTE() replaces specific text within a string. The REPLACE() function replaces text at a specific position.

Conclusion

As you have seen and I hope now appreciate and will use, the TEXT() function is important because it can embed formatted values into dynamic labels, reports, templates, and other things, and, most noticeably, it makes sure that your data appears exactly as you intend it to appear. It's purely cosmetic, but it makes a big difference. All that said, I do want to stress, however, for calculations and analysis, it is best to keep the original numeric values intact elsewhere in the spreadsheet to maintain accuracy and flexibility, and just apply the cosmetic part to the final result.

For next steps, I recommend taking our Data Analysis in Excel course to keep practicing your skills and our Excel Fundamentals skill track to all the different functions available in the latest Excel versions.