TEXT() Function for Formatting Numbers and Dates in Excel

 

Excel is not just about crunching numbers — it’s also about presenting data in a readable and professional format. Whether you want dates to appear as “March 14, 2025” instead of 14/03/2025, or want to display large numbers with commas, percentages, or currency signs, Excel’s TEXT() function is your ultimate tool.

In this blog, we’ll dive deep into the TEXT() function in Excel — what it is, how it works, and how to use it effectively to format numbers and dates with real-life examples.

---

📌 What is the TEXT() Function?

The TEXT() function in Excel allows you to convert a number or date into text using a specific format you define. It’s incredibly useful when you're creating dashboards, reports, or templates where data must look clean, consistent, and easy to read.

✅ Syntax:

=TEXT(value, format_text)

• value: The number, date, or cell reference you want to format.

• format_text: The custom format you want to apply — wrapped in double quotes.

---

🎯 Why Use TEXT()?

• Format numbers with commas, decimals, or currency

• Convert dates to readable month-day-year formats

• Show percentages, fractions, or phone numbers

• Build custom text strings for dashboards

• Combine text and numbers/dates in a single cell

Let’s now see the most popular use cases in action.

---

🧪 Formatting Numbers with TEXT()

🔹 1. Add Commas to Large Numbers

If A2 contains 1250000, and you want it to display as 1,250,000:

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

This makes large numbers readable, especially in financial or sales reports.

---

🔹 2. Format as Currency

To display a number like 1250.5 as $1,250.50:

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

For INR (₹):

=TEXT(A2, "₹#,##0.00")

You can replace the currency symbol with any local symbol.

---

🔹 3. Show as Percentage

If A2 contains 0.86 and you want to show 86%:

=TEXT(A2, "0%")

To include decimal places like 86.00%:

=TEXT(A2, "0.00%")

---

🔹 4. Display as Phone Number

If A2 has 9876543210, you can show it as (987)-654-3210:

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

This is useful in contact lists or CRM dashboards.

---

🧪 Formatting Dates with TEXT()

Excel stores dates as serial numbers — formatting them properly makes them readable.

🔹 1. Day-Month-Year Format

If A2 = 14-May-2025, then:

=TEXT(A2, "dd-mm-yyyy")

Returns: 14-05-2025

---

🔹 2. Full Date Format

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

Returns: Wednesday, May 14, 2025

Great for formal letters, reports, and headers.

---

🔹 3. Short Month and Year Only

=TEXT(A2, "mmm yyyy")

Returns: May 2025

Used in sales dashboards or monthly summaries.

---

🔹 4. Day Name Only

=TEXT(A2, "dddd")

Returns: Wednesday

Useful for attendance or scheduling tasks.

---

🔁 Combine TEXT() with Other Functions

TEXT() shines when you combine it with other formulas to create dynamic outputs.

---

🔹 1. Invoice Date Stamp

="Invoice Date: " & TEXT(TODAY(), "dd-mmm-yyyy")

Returns:
Invoice Date: 14-May-2025

---

🔹 2. Salary Slip

If A2 = salary (e.g., 45000) and B2 = date (e.g., 01-Apr-2025):

="Salary for " & TEXT(B2, "mmmm yyyy") & " is ₹" & TEXT(A2, "#,##0.00")

Returns:
Salary for April 2025 is ₹45,000.00

---

🧩 Format Text Codes Reference

Code	Description	Example Output
dd	Day (2-digit)	05
ddd	Day name (short)	Wed
dddd	Day name (full)	Wednesday
mm	Month (2-digit)	03
mmm	Month name (short)	Mar
mmmm	Month name (full)	March
yyyy	Year (4-digit)	2025
0.00	Two decimal places	86.50
#,##0	Number with commas	1,000
"$"#,##0.00	Currency format	$1,000.00

---

🚫 Things to Watch Out For

• TEXT() returns text, not a number. So you can’t use the result in math calculations unless converted back.

• If you format dates using TEXT(), it won’t auto-update format based on regional settings — it stays static as per your formula.

• Double-check for extra spaces or incorrect formats; they can break the output.

---

#ExcelTextFunction #ExcelDateFormatting #ExcelNumberFormatting #TextFunctionInExcel #ExcelTipsAndTricks #ExcelFormatting #ExcelDashboardDesign #OfficeExcelHelp