Sometimes the value in Excel is correct, but you want to display it in a specific format.
For example:
โข "17-Aug-2026" โ "August 2026"
โข "0.25" โ "25%"
โข "125000" โ "โน125,000"
That's where the TEXT() function is useful.
๐ What is the TEXT() Function?
TEXT() converts a number or date into text using a format you specify.
Syntax:
=TEXT(value, format_text)
โ ๏ธ The result of TEXT() is text, not a numeric value.
๐ Example 1 โ Format a Date
Suppose:
A2 = 17-Aug-2026
Formula:
=TEXT(A2,"dd-mm-yyyy")
Result:
17-08-2026
Another example:
=TEXT(A2,"mmmm yyyy")
Result:
August 2026
๐ Example 2 โ Extract the Month Name
=TEXT(A2,"mmmm")
Result:
August
Short month name:
=TEXT(A2,"mmm")
Result:
Aug
๐ Example 3 โ Format Numbers
Suppose:
A2 = 125000
Formula:
=TEXT(A2,"#,##0")
Result:
125,000
๐ Example 4 โ Format Currency
=TEXT(A2,"โน#,##0")
Result:
โน125,000
You can also use:
=TEXT(A2,"โน#,##0.00")
Result:
โน125,000.00
๐ Example 5 โ Format Percentage
Suppose:
A2 = 0.25
Formula:
=TEXT(A2,"0%")
Result:
25%
With decimals:
=TEXT(A2,"0.00%")
Result:
25.00%
๐ Example 6 โ Combine Text with a Date
Suppose:
A2 = 17-Aug-2026
Formula:
="Report generated on "&TEXT(A2,"dd-mmm-yyyy")
Result:
Report generated on 17-Aug-2026
This is especially useful for dynamic report titles and dashboard labels.
๐ Common Date Format Codes
โข dd โ Day
โข ddd โ Short day name
โข dddd โ Full day name
โข mm โ Month number
โข mmm โ Short month name
โข mmmm โ Full month name
โข yy โ Two-digit year
โข yyyy โ Four-digit year
Example:
=TEXT(A2,"dddd, dd mmmm yyyy")
Result:
Monday, 17 August 2026
๐ Important Difference
Changing the cell's number format only changes how the value looks.
TEXT() actually converts the value into text.
For example:
=TEXT(A2,"โน#,##0")
may look like a currency value, but the result is text and shouldn't be used directly for mathematical calculations.
๐ Real-World Uses
โข Format dates in reports.
โข Create dynamic dashboard titles.
โข Display currency values.
โข Format percentages.
โข Create readable messages.
โข Combine numbers or dates with text.
๐ Common Mistake
โ Using TEXT() when you still need to perform calculations on the result.
If you only need to change how a number or date looks, consider using Cell Format โ Number Format instead.
โ Quick Tip
Remember:
TEXT() โ Convert a value into formatted text
Examples:
โข
=TEXT(A2,"mmmm yyyy") โ August 2026โข
=TEXT(B2,"โน#,##0") โ โน125,000โข
=TEXT(C2,"0.00%") โ 25.00%Double Tap โค๏ธ For More