TEXT function

The TEXT function in Excel is used to convert a number or a date into a text string with a specific format. This function allows you to display numbers, dates, and times in various formats based on your preferences. It’s particularly useful when you want to present data in a readable or custom format, such as currency, percentages, dates, or even custom formats.

Syntax:

=TEXT(value, format_text)

Parameters:

• value (required): The numeric value, date, or time that you want to format as text.
• format_text (required): The format in which you want to display the value. This should be a string enclosed in double quotation marks. The format can be a predefined format (like currency or date) or a custom format.

Key Points:

• The TEXT function is useful for displaying data in a more readable or specific format, such as for reports, charts, or custom displays.
• The TEXT function converts the value into text. This means that any numeric or date values converted will no longer be recognized as numbers or dates in calculations.
• The format_text argument can include various formatting codes for numbers, dates, and times, as well as custom formatting.

Common Format Codes:

• Number Formats:
  • "0": Display a number with zero decimal places.
  • "0.00": Display a number with two decimal places.
  • "#,##0": Format with commas for thousands (e.g., 1,000).
  • "$0": Format as currency (e.g., $10).
  • "$#,##0.00": Currency with thousand separators and two decimal places.
  • "0%": Display as a percentage (e.g., 10%).
• Date Formats:
  • "mm/dd/yyyy": Format as a date (e.g., 01/15/2025).
  • "dddd, mmmm dd, yyyy": Full date format (e.g., Monday, January 15, 2025).
  • "mmm dd, yyyy": Date in abbreviated month form (e.g., Jan 15, 2025).
  • "yyyy-mm-dd": Date in ISO format (e.g., 2025-01-15).
• Time Formats:
  • "hh:mm AM/PM": Format time with AM/PM (e.g., 03:30 PM).
  • "hh:mm:ss": Display time in hours, minutes, and seconds (e.g., 03:30:45).

Examples of Usage:

1. Formatting a Number

If cell A1 contains the number 1234.56 and you want to display it with two decimal places:

=TEXT(A1, "0.00")

This will return:

1234.56

Explanation:

• The number is formatted with two decimal places.

2. Currency Format

If cell B1 contains the number 1000, and you want to display it as currency:

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

This will return:

$1,000.00

Explanation:

• The number is formatted as currency with thousand separators and two decimal places.

3. Formatting Dates

If cell C1 contains the date 2025-01-15 and you want to display it as “January 15, 2025”:

=TEXT(C1, "mmmm dd, yyyy")

This will return:

January 15, 2025

Explanation:

• The date is formatted in a custom format that shows the full month name, day, and year.

4. Percentage Format

If cell D1 contains the number 0.25 and you want to display it as a percentage:

=TEXT(D1, "0%")

This will return:

25%

Explanation:

• The value is displayed as a percentage (25%).

5. Time Format

If cell E1 contains the time 14:30:00 and you want to display it as “2:30 PM”:

=TEXT(E1, "hh:mm AM/PM")

This will return:

2:30 PM

Explanation:

• The time is formatted with a 12-hour clock and AM/PM notation.

Advanced Example: Combining Text and Numbers

You can combine text and formatted numbers using the TEXT function. For example, if cell F1 contains the value 1234.567, and you want to display it in a sentence with a custom format:

="The total is " & TEXT(F1, "$#,##0.00")

This will return:

The total is $1,234.57

Explanation:

• The number in F1 is formatted as currency and then combined with the text “The total is”.

Notes:

• Non-Numeric Values: If the value passed to the TEXT function is not a number, date, or time, the function will return an error.
• Text Conversion: Remember that the result of the TEXT function is a text string, so if you need to use the result in calculations, you’ll need to convert it back to a number using the VALUE function.
• Date and Time Handling: The TEXT function works well with dates and times, but if the date is not properly recognized (due to regional settings or formatting issues), you may need to ensure the date is properly inputted.

Related Functions:

• VALUE: Converts a text string that represents a number into an actual number.
• DATE / TIME: These functions can be used in conjunction with the TEXT function to manipulate and format dates and times.
• CONCAT / TEXTJOIN: Combine text values with formatted numbers or dates into one string.

The TEXT function is an excellent tool for custom formatting of numbers, dates, times, and text, allowing you to present data in a way that suits your needs for reporting, presentation, or analysis in Excel.