MS Excel: TEXT function for text formatting
- Fakhriddinbek
- 3 days ago
- 2 min read
The TEXT function in Excel allows you to convert numbers, dates, and times into formatted text. It’s essential when you want to display numeric values in a specific format—like currency, percentages, or custom date formats—while retaining flexibility in combining data with other strings.
It is commonly used in:
Creating readable reports
Preparing labels or dynamic text
Controlling date/time/number presentation in formulas
Syntax
=TEXT(value, format_text)
Parameters:
Argument | Description |
value | Required. The number, date, or time you want to format. |
format_text | Required. A text string that defines the format you want to apply. |
Returns: The formatted number as text.
Examples with Table
Formula | Description | Result |
=TEXT(1234.56, "0.00") | Number with 2 decimals | 1234.56 |
=TEXT(1234.56, "$#,##0.00") | Currency format | $1,234.56 |
=TEXT(TODAY(), "dd/mm/yyyy") | Date format | 02/05/2025 (example) |
=TEXT(NOW(), "hh:mm AM/PM") | Time format | 10:45 AM (example) |
=TEXT(0.85, "0%") | Percentage | 85% |
=TEXT(5.678, "# ?/?") | Fraction format | 5 2/3 |
=TEXT(1234567, "0.00E+00") | Scientific notation | 1.23E+06 |
Format Codes Reference
Number Formats
Format Code | Output Example |
"0" | 123 |
"0.00" | 123.45 |
"#,##0" | 1,000 |
"$#,##0.00" | $1,000.00 |
Date Formats
Format Code | Output Example |
"mm/dd/yyyy" | 05/02/2025 |
"dddd" | Friday |
"mmm dd, yyyy" | May 02, 2025 |
Time Formats
Format Code | Output Example |
"hh:mm" | 14:30 |
"hh:mm AM/PM" | 02:30 PM |
"hh:mm:ss" | 14:30:45 |
Combine with Text
You can use TEXT to create dynamic strings:
="Report as of " & TEXT(TODAY(), "mmmm dd, yyyy")
Result: "Report as of May 02, 2025"
Use Cases
Scenario | Why Use TEXT |
Exporting to external systems | Force specific formats in CSV or reports |
Creating human-readable dashboards | Display dates/numbers in clean formats |
Merging values in formulas | Combine text with numbers or dates |
Custom labels or titles | Insert formatted values into text strings |
Notes & Limitations
The TEXT function returns text, so results are not usable for calculations unless converted back.
Format codes must be wrapped in double quotes.
If formatting a cell value that already has a format, TEXT will override it within the formula only.
Related Functions
Function | Description |
TEXTJOIN | Joins text items with delimiter |
VALUE | Converts text to number |
CONCAT | Joins text strings together |
DATE, TIME | Builds date/time from parts |
FIXED | Rounds number as text with formatting |
Summary
Feature | Description |
Function Name | TEXT |
Purpose | Format numbers, dates, or times as text |
Output | Text only |
Format Options | Rich formatting: numbers, dates, times, currency |
Final Thoughts
The TEXT function is an essential tool in Excel for precise control over data presentation. Whether you're building dashboards, generating reports, or combining values in formulas, TEXT ensures your numbers and dates look exactly the way you want.
Comentários