Excel TEXT Function
The TEXT function in Microsoft Excel formats a number or date as a text string according to a specified format. It is useful for creating custom displays, such as formatted dates, currencies, or percentages in reports.
Key Topics
Overview of TEXT Function
The TEXT function converts numeric or date values into text with user-defined formatting, such as adding decimal places, currency symbols, or date patterns. It is often used in concatenation or to ensure consistent data presentation.
TEXT Function Syntax
The syntax for the TEXT function is as follows:
Syntax: =TEXT(value, format_text)
Parameters:
- value: The number or date to format (required).
- format_text: The format code to apply, enclosed in quotes (required).
Implementation Examples
Below are examples of how to use the TEXT function in different scenarios.
Example 1: =TEXT(B2, "$#,##0.00") — Formats B2 as currency with two decimal places.
Example 2: =TEXT(C2, "mm/dd/yyyy") — Formats C2 as a date in MM/DD/YYYY format.
Example 3: =CONCAT("Total: ", TEXT(B2, "$#,##0")) — Combines text with B2 formatted as currency.
Sample Dataset (Excel-style View)
Below is a demo dataset styled like Excel, showing how the TEXT function can be used to format sales amounts as currency.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | Formatted Sales |
| 2 | Laptop | 1500 | =TEXT(B2, "$#,##0.00") |
| 3 | Phone | 800.5 | =TEXT(B3, "$#,##0.00") |
| 4 | Tablet | 1200 | =TEXT(B4, "$#,##0.00") |
| 5 | Monitor | 900.75 | =TEXT(B5, "$#,##0.00") |
| 6 | Printer | 1100 | =TEXT(B6, "$#,##0.00") |
Note: In the dataset, column C uses =TEXT(B2, "$#,##0.00") to format the sales amount in B2 as currency with two decimal places. The formula ensures consistent formatting across rows.
Key Takeaways
- The TEXT function formats numbers or dates as text with custom formats.
- It uses format codes like "$#,##0.00" for currency or "mm/dd/yyyy" for dates.
- It is often used with CONCAT to create formatted outputs.
- Common uses include formatting reports, dashboards, or concatenated strings.
- The sample dataset demonstrates TEXT applied to format sales as currency.