How to Use the TEXT Function in Excel for Formatting

TEXT function in Excel plays a very important role in data cleaning and analyzing. it is crucial to clean the data and correct the formatting before inserting formulas. Formatting make spreadsheets easy to read and analyze.

There are cases when we want to display dates in a different way (yyyy-mm-dd) to (dd/mm/yyyy), or format numbers with currency symbol (12.34 to $12.34). We may also need to customize percentages. There are many more use cases and the TEXT function in Excel can help us do all that.

In this guide, we will understand the basics of TEXT function, use it for formatting with simple explanations and real world examples. We will also give you practice questions in a downloadable Excel. So read till the end, and master the art of turning raw data into beautifully formatted, presentation-ready information.

What is the TEXT Function in Excel?

The TEXT function is used to convert a number or a value into text in a specific format. Its basic syntax is as below:

=TEXT(value, format_text)

• value: This is the number, date, or cell reference that we want to format.
• format_text: It is a string that defines how we want to format the value.

It will become clear with the upcoming examples.

Why Use the TEXT Function in Excel?

You may have noticed that Excel has a default format for number or dates. But we may need to change to a different format. The TEXT function lets us do that exactly as we want our data to look. For example:

• Format a date, 01/01,2025) as “Monday, 01 January 2025”
• Add currency symbols to values (12.34 to $12.34)
• Show numbers with leading zeros (e.g., invoice numbers such as 000073)
• Combine text and numbers in a single cell

Let’s look  at some examples.

1. Formatting Dates with TEXT Function in Excel

Let’s say cell A1 contains the date 2025-01-01. We can format it in various ways using the TEXT function:

=TEXT(A1, "dd/mm/yyyy")     → 01/01/2025
=TEXT(A1, "dddd, dd mmmm yyyy") → Wednesday, 01 January 2025
=TEXT(A1, "mmm-yy")           → Jan-25

You can play around with these formats and try as per your own need. Some common date format codes:

• dd = day (e.g., 01)
• mmm = short month (e.g., Jan)
• mmmm = full month (e.g., January)
• yy = two-digit year (e.g., 25)
• yyyy = four-digit year (e.g., 2025)
• dddd = full day name (e.g., Monday)

2. Formatting Numbers

There can be various ways we may want to format a number. For example –

Add Currency:

If cell A2 has the value 1500, use TEXT function like this to re-format:

=TEXT(A2, "$#,##0.00")     → $1,500.00

Percentage:

If cell A3 contains 0.875, we can format A3 as a percentage like this:

=TEXT(A3, "0.0%")           → 87.5%

Add Leading Zeros:

If A4 contains 23, and we want to show it as a 5-digit code since it is an invoice number:

=TEXT(A4, "00000")          → 00023

Large Numbers with Commas:

Huge numbers like 10 million or 13 billion are difficult to read as we have to count zeros. We can make it easy by inserting commas –

=TEXT(A2, "#,###") → 1,500

We can play around with formatting in all these examples as per our need.

3. Combine Text and Formatting

Now, let’s discuss one of the most powerful uses of TEXT – combining formatted values with other text.

Suppose If A1 has a date (1 Jan 2025) and B1 has a sales number (1500):

="Sales on " & TEXT(A1, "dd-mmm-yyyy") & " were $" & TEXT(B1, "#,##0.00")

Output:

Sales on 01-Jan-2025 were $1,500.00

This is very handy when it comes to creating dashboards, reports, and emails.

4. Format Time Values with TEXT Function in Excel

We can also use TEXT to format time stamps. For example –

If A5 has time 15:45:00, use:

=TEXT(A5, "hh:mm AM/PM")     → 03:45 PM
=TEXT(A5, "hh:mm:ss")        → 15:45:00

Use these popular time format codes:

• hh = hours
• mm = minutes
• ss = seconds
• AM/PM = shows time in 12-hour format

5. Format Phone Numbers or IDs

If A6 has 9876543210, format it as an Indian mobile number:

=TEXT(A6, "00000-00000")     → 98765-43210

Or U.S. format:

=TEXT(A6, "(000) 000-0000")

This is a great feature, right? 🙂

6. Format Fractions with Text Function in Excel

We can also use TEXT function to convert decimal values to fractions like this –

If A7 contains 0.75:

=TEXT(A7, "# ?/?")            → 3/4

7. Limitations of TEXT Function in Excel

• We can not use the output value of TEXT for further calculation unless we convert it back. This is because the output of TEXT is stored as text regardless of what it was before.
• It only changes
• The actual value does not change, only the looks change. For ex, when we change 12.3457 to 12.345 using TEXT, actual value is still 12.3457.
• Make sure the formatting string is in double quotes or we will get errors.

Practice Questions

Now, it is time to practise yourself. Try these on your own to get comfortable:

1. Format the date 01/02/2025 as Saturday, 01 February 2025
2. Format the number 1234567.89 as ₹1,234,567.89
3. Add leading zeros to make the number 45 appear as 000045
4. Combine: A1 has the value John, B1 has 01/06/2025, and C1 has 4500. Create: John made ₹4500.00 on 01-June-2025
5. Convert the decimal 0.333 into a fraction like 1/3

Final Tips

• Use TEXT in templates and dashboards to keep things tidy.
• Emphasizing again that formatting does not change data – it only changes just how it appears.
• It is recommended to double-check the TEXT function in Excel formulas if you get a #VALUE! error. It usually means there’s a typo in the format string.

Conclusion

One of Excel’s most underutilized features is the TEXT function. It assists in converting unintelligible data into clear, comprehensible, and polished content. Gaining proficiency with TEXT can help you save time and add a polished touch to your work, whether you’re creating a report, dashboard, or even mail-merge values.

Hence, try out the aforementioned examples, modify them, and personalize them. Regular use will make using TEXT for formatting instinctive.

Here is a practice workbook that can be downloaded – Excel_TEXT_Function_Practice. Please leave your queries in comments.

Happy Excelling!

Want more Excel tips? Explore further with our guides on below topics –

1. Mastering IF, IFs, AND, OR Functions in Excel : A Beginner to Intermediate Guide

2. Power Query in Excel : Beginner’s Guide for Data Transformation

3. How to Create a Dashboard in Excel : A Step-by-Step Guide for Beginners

4. Microsoft Guide on Text Function