How to Use the TEXT Function in Excel

Microsoft Excel is a powerful tool for working with numbers, dates, and data of all kinds. However, one common challenge users face is controlling how values appear in cells—especially when working with dates, times, currencies, percentages, and custom number formats. This is where the TEXT function becomes extremely useful.

The TEXT function allows you to convert a numeric value into text while applying a specific format. Whether you want to display dates in a readable format, format currency consistently, or combine numbers with text in formulas, the TEXT function provides precise control over data presentation.

In this comprehensive guide, you will learn how to use the TEXT function in Excel, understand its syntax, explore real-world examples, and discover best practices for using it effectively.

What Is the TEXT Function in Excel?

The TEXT function converts a numeric value into text and applies a formatting pattern of your choice.

Why Use the TEXT Function?

Excel automatically formats numbers based on cell settings, but formulas often return raw values. The TEXT function allows you to:

• Format dates inside formulas
• Display numbers with commas, decimals, or currency symbols
• Control percentage and time formatting
• Combine text and formatted numbers in one cell
• Standardize report output without changing cell formatting

Important Note

Once a value is converted to text using the TEXT function, it can no longer be used in calculations unless converted back to a number.

TEXT Function Syntax

=TEXT(value, format_text)

Arguments Explained

Understanding Format Codes in TEXT Function

The power of the TEXT function comes from its format codes, which define how the value appears.

Common Format Code Categories

• Number formats
• Date formats
• Time formats
• Currency and accounting formats
• Percentage formats
• Custom formats

Formatting Numbers Using TEXT

Example 1: Adding Thousand Separators

=TEXT(1234567,"#,##0")

Result:

1,234,567

This is useful for reports and dashboards where readability matters.

Example 2: Controlling Decimal Places

=TEXT(1234.567,"#,##0.00")

Result:

1,234.57

Excel rounds the number automatically based on the format.

Example 3: Formatting Large Numbers

=TEXT(1500000,"0.0,, ""M""")

Result:

1.5 M

This is especially helpful for financial summaries and executive reports.

Formatting Currency Values

Example: Displaying Currency with Symbols

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

Result:

$2,500.00

Using Different Currency Symbols

=TEXT(2500,"€#,##0.00")

Result:

€2,500.00

You can also use regional formats depending on your system settings.

Formatting Percentages with TEXT

Example: Convert Decimal to Percentage

=TEXT(0.875,"0%")

Result:

88%

With Decimal Precision

=TEXT(0.875,"0.00%")

Result:

87.50%

This is helpful when generating textual summaries or KPI descriptions.

Formatting Dates Using TEXT

Dates are stored as numbers in Excel, which makes the TEXT function extremely useful for controlling date display.

Common Date Format Codes

Example 1: Standard Date Format

=TEXT(A1,"dd/mm/yyyy")

Result:

15/01/2026

Example 2: Long Date Format

=TEXT(A1,"dddd, mmmm dd, yyyy")

Result:

Thursday, January 15, 2026

This is perfect for reports, invoices, and automated emails.

Example 3: Month and Year Only

=TEXT(A1,"mmmm yyyy")

Result:

January 2026

Formatting Time Values

Excel stores time as fractions of a day, and the TEXT function allows precise control over time display.

Example 1: Standard Time

=TEXT(A1,"hh:mm")

Result:

14:30

Example 2: Time with Seconds

=TEXT(A1,"hh:mm:ss")

Example 3: AM/PM Format

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

Combining TEXT with Other Functions

One of the most powerful uses of the TEXT function is combining formatted values with text.

Example: Sales Summary Sentence

="Total sales for "&TEXT(A1,"mmmm yyyy")&" were "&TEXT(B1,"$#,##0")

Result:

Total sales for January 2026 were $45,000

This technique is widely used in dashboards and automated reports.

Using TEXT with TODAY and NOW

Example: Display Today’s Date as Text

=TEXT(TODAY(),"dddd, mmmm dd")

Result:

Thursday, January 15

Example: Timestamp Formatting

=TEXT(NOW(),"dd/mm/yyyy hh:mm")

TEXT Function vs Cell Formatting

Use TEXT when formatting must be part of a formula. Use cell formatting when calculations are still needed.

Common Mistakes When Using TEXT

1. Forgetting Quotation Marks

Incorrect:

=TEXT(A1,dd/mm/yyyy)

Correct:

=TEXT(A1,"dd/mm/yyyy")

2. Using TEXT in Calculations

Once formatted, values become text and cannot be summed or averaged.

Bad practice:

=SUM(TEXT(A1,"0.00"),TEXT(A2,"0.00"))

3. Locale Differences

Some formats vary by regional settings, especially for decimal separators and currencies.

Practical Business Use Cases

• Financial reports with consistent currency formatting
• HR reports displaying hire dates in readable formats
• Dashboards with dynamic text labels
• Invoices and quotations
• Exporting Excel data to text-based systems

Best Practices for Using the TEXT Function

• Use TEXT only for presentation, not calculations
• Keep formatting simple and readable
• Document complex formulas
• Combine TEXT with CONCAT or & carefully
• Test formats across different regional settings

Alternatives to the TEXT Function

• Cell Formatting for pure numeric data
• Custom Number Formats
• FORMAT Cells dialog
• TEXTJOIN for advanced text concatenation

Final Thoughts

The TEXT function in Excel is an essential tool for controlling how numbers, dates, and times appear in formulas. While it should be used carefully—since it converts values to text—it is invaluable for reporting, dashboards, and automated summaries.

By mastering the TEXT function, you gain the ability to present Excel data clearly, professionally, and consistently, making your spreadsheets more user-friendly and effective.

Whether you are a beginner learning Excel basics or an advanced user building complex reports, the TEXT function deserves a permanent place in your Excel skill set.