The `TEXT` function in Excel is used to convert a numerical value into text in a specified format, primarily useful for displaying numbers in a more readable format or for custom formatting in reports. Here are the basics of using the `TEXT` function:
Syntax:
TEXT(value, format_text)
- value: The numerical value that you want to convert to text.
- format_text: A string that specifies the desired format for the number. This uses the same format codes that are used in Excel’s built-in formatting options.
Commonly Used Format Codes:
- General Number Formats:
- `”0″`: Displays numbers with no decimal places.
- `”0.00″`: Displays numbers with two decimal places.
- `”#,##0″`: Displays numbers with thousand separators and no decimal places.
- `”#,##0.00″`: Displays numbers with thousand separators and two decimal places.
- Date and Time Formats:
- `”mm/dd/yyyy”`: Month/day/year (e.g., 03/14/2021).
- `”dddd, mmmm dd, yyyy”`: Full day name, month name, day, year (e.g., Wednesday, March 14, 2021).
- `”hh:mm:ss AM/PM”`: Time format with AM/PM designation.
- Currency Formats:
- `”$#,##0.00″`: Sets a number to currency format with two decimal places.
- Percentage Formats:
- `”0%”`: Converts the number into a percentage with no decimal places.
- `”0.00%”`: Converts the number into a percentage with two decimal places.
Examples:
=TEXT(1234.567, "#,##0.00")
- Basic Number Format:
Output: `”1,234.57″`
=TEXT(TODAY(), "mm/dd/yyyy")
- Date Format:
Output: A formatted string of today’s date in the “MM/DD/YYYY” format.
=TEXT(NOW(), "hh:mm AM/PM")
- Time Format:
Output: A formatted string of the current time in “HH:MM AM/PM” format.
=TEXT(1234.567, "$#,##0.00")
- Currency Format:
Output: `$1,234.57`
=TEXT(0.1234, "0.00%")
- Percentage Format:
Output: `”12.34%”`
Tips:
=TEXT(A1,"$#,##0.00") & " USD"
- Always use quotation marks around `format_text`.
- The `TEXT` function changes a number to text, which can affect calculations. You can’t use the result of a `TEXT` function directly for calculations unless you convert it back to a number.
- You can combine the `TEXT` function with other strings to create more descriptive output. For example:
This will display the number in cell A1 as currency followed by “USD”.
By utilizing the `TEXT` function with the appropriate format codes, you can effectively control the display of numerical data in your Excel sheets.
Unlock Your Potential
Excel
Access
Power BI
