Summary
The Excel TEXT function converts a numeric value to text format by applying formatting defined in a text string. This function is useful for creating text representations of numbers that need to appear in a specific format.
Syntax
=TEXT(value, format_text)
- value: The numeric value to be formatted as text
- format_text: The format to apply, enclosed in quotation marks
Return value
The numeric value formatted as a text string according to the specified format.
How to use
TEXT is ideal for formatting numbers, dates, and times as text in a specific format, especially in situations where maintaining a consistent presentation of data is necessary. It’s commonly used in reporting, data visualization, and concatenating strings with numbers.
Examples
Simple TEXT
Formatting a Number with Two Decimal Places: To format a number as text with two decimal places:
=TEXT(A1, "0.00")
If A1 contains 123.456, this formula returns “123.46”.
TEXT for Date Formatting
Converting a Date to a Specific Text Format: Using TEXT to format a date:
=TEXT(B2, "dd-mm-yyyy")
If B2 is a date value (like 1/1/2024), it returns “01-01-2024”.
TEXT with Currency Formatting
Formatting Numbers as Currency Text: Applying currency format:
=TEXT(C2, "$#,##0.00")
Converts the number in C2 to currency format, like “$1,234.56”.
TEXT for Time Formatting
Formatting Time Values as Text: Converting time to a text string:
=TEXT(D2, "hh:mm AM/PM")
If D2 is a time value, this formula returns it in a 12-hour format, like “05:30 PM”.
TEXT in Concatenations
Combining Text and Numbers in a Sentence: Using TEXT in concatenation for readable output:
=E2 & " scored " & TEXT(F2, "0.0") & " in the test"
Combines text with a number from F2 formatted to one decimal place.
TEXT for Percentage Formatting
Displaying Numbers as Percentages in Text: Formatting a decimal as a percentage:
=TEXT(G2, "0.0%")
Converts a decimal in G2 (like 0.75) to “75.0%”.
Additional Notes
- TEXT is useful for creating text values where numbers must be in a consistent format, especially in templates and reports.
- The function converts numbers to text, which means they cannot be used for calculations without converting back to a number.
- TEXT does not change the underlying value, only its display format as a text string.