Different Uses of TEXT Function in Excel | Master Data Formatting Easily

The TEXT function in Excel is one of the most powerful and underrated tools for formatting data. It allows you to convert numbers, dates, and times into text with custom formatting — perfect for reports, dashboards, and automation.

🔹 What is the TEXT Function?

Syntax:

=TEXT(value, format text)

• value: The number, date, or time you want to format.
• format_text: The formatting code you apply (like “dd/mm/yyyy” or “$#,##0.00”).

🔸 1. Format Dates

Convert any date into a custom format:

=TEXT(A1, "dd-mm-yyyy")  
=TEXT(A1, "dddd")           → Returns the day name like Monday  
=TEXT(A1, "mmm yyyy")       → Jan 2025  

🔸 2. Format Time

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

This is useful when you want to display time in a readable format.

🔸 3. Add Currency Symbols

=TEXT(A1, "$#,##0.00")  
=TEXT(A1, "₹#,##0.00")  

Makes numeric data more user-friendly in financial reports.

🔸 4. Add Leading Zeros

=TEXT(A1, "00000")  

If A1 contains 52 → Result: 00052
✅ Ideal for ID numbers, PINs, or product codes.

🔸 5. Format Percentages

=TEXT(A1, "0.00%")  

If A1 is 0.875 → Result: 87.50%

🔸 6. Combine Text with Numbers

="Final Score: " & TEXT(A1, "0.00")  

Helpful when preparing dashboards or auto-updating summaries.

🔸 7. Format Phone Numbers or Custom Patterns

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

Turns 9876543210 into (987)-654-3210

🔸 8. Format for Export or Reports

=TEXT(TODAY(),"dd-mm-yyyy")  
=TEXT(NOW(),"dd/mm/yyyy hh:mm AM/PM")  

Makes data consistent when exporting to PDFs or print views.

💡 Pro Tips:

• Use TEXT inside formulas like CONCAT, IF, and VLOOKUP to make your outputs smarter.
• TEXT converts values to text, so it won’t work in calculations unless re-converted.

Want to speed up your workflow even more? Combine TEXT() with functions like TODAY(), NOW(), ROUND(), or even IF() for dynamic Excel magic! ✨

Example: