TEXT() in Advanced Excel

The TEXT() function in Excel is used to format numbers, dates, and times into a specific format, making it easier to display data according to your requirements. It is a very useful function when you need to present data in a particular format, such as currency, date, or percentage. In this tutorial, we will explore the TEXT() function with real-time examples.

TEXT() Function

The TEXT() function allows you to format a number or date value as text in a specified format.

TEXT() Syntax

TEXT(value, format_text)
  • value: The number or date that you want to format.
  • format_text: The format in which you want to display the number or date (e.g., currency, date format, time format).

Real-Time Example of TEXT()

Scenario: You have a sales report with numbers, and you want to display them as currency with a dollar sign and two decimal places.

        Sales Amount
        1200
        9876
        432

Steps:

  1. In cell B1, enter the following formula to format the sales amount as currency:
    2. =TEXT(A1, "$#,##0.00")
  2. Explanation: The format "$#,##0.00" tells Excel to display the value as currency with a dollar sign, thousands separators, and two decimal places. For "1200", the result will be "$1,200.00", for "9876" it will be "$9,876.00", and for "432" it will be "$432.00".

Formatting Dates with TEXT()

The TEXT() function can also be used to format dates. You can display dates in various formats, such as "MM/DD/YYYY", "DD-MMM-YYYY", and many more.

Real-Time Example of Formatting Dates

Scenario: You have a list of dates, and you want to display them in the "DD-MMM-YYYY" format.

        Date
        01/15/2024
        02/25/2024
        03/05/2024

Steps:

  1. In cell B1, enter the following formula to format the date:
    2. =TEXT(A1, "DD-MMM-YYYY")
  2. Explanation: The format "DD-MMM-YYYY" tells Excel to display the date as day-month-year, with the month in three-letter abbreviation. For "01/15/2024", the result will be "15-Jan-2024", for "02/25/2024" it will be "25-Feb-2024", and for "03/05/2024" it will be "05-Mar-2024".

Formatting Time with TEXT()

The TEXT() function can also be used to format time values. You can display times in various formats, such as "HH:MM AM/PM", "HH:MM:SS", and others.

Real-Time Example of Formatting Time

Scenario: You have a list of times, and you want to display them in the "HH:MM AM/PM" format.

        Time
        14:30
        09:45
        18:15

Steps:

  1. In cell B1, enter the following formula to format the time:
    2. =TEXT(A1, "hh:mm AM/PM")
  2. Explanation: The format "hh:mm AM/PM" tells Excel to display the time in 12-hour format with "AM" or "PM". For "14:30", the result will be "02:30 PM", for "09:45" it will be "09:45 AM", and for "18:15" it will be "06:15 PM".

Combining TEXT() with Other Functions

You can also combine the TEXT() function with other functions to create more complex formulas. For example, you can use the TEXT() function to concatenate formatted numbers or dates with other text.

Real-Time Example of Combining TEXT()

Scenario: You want to create a custom message that includes a sales amount formatted as currency and a date formatted as "DD-MMM-YYYY".

        Sales Amount: 1200    Date: 01/15/2024

Steps:

  1. In cell B1, enter the following formula to create the custom message:
    2. = "Sales Amount: " & TEXT(A1, "$#,##0.00") & " Date: " & TEXT(A2, "DD-MMM-YYYY")
  2. Explanation: This formula combines text and formatted values. It will display the result as "Sales Amount: $1,200.00 Date: 15-Jan-2024".

Key Differences Between TEXT() and Other Formatting Methods

  • TEXT(): Formats the data as text, which is useful for creating custom formats and combining with other text data.
  • Number Formatting: Direct number formatting (e.g., "Format Cells" option) changes how the number appears but keeps it as a numeric value for calculations.

Conclusion

The TEXT() function is extremely versatile and useful for formatting numbers, dates, and times in Excel. Whether you are working with currency, custom date formats, or time values, the TEXT() function can help you present your data exactly as you need. By combining it with other functions, you can further customize your Excel worksheets to meet your specific needs.




Advertisement