Excel TEXT function

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.

Related Functions

Excel VALUE function

The Excel VALUE function converts a text string that represents a number into a numeric value, crucial for calculations.

Content Navigation