Excel Training London: text

The TEXT function in Excel is used to convert a value to text in a specific number format. It’s particularly useful when you want to display numbers, dates, or times in a specific format within a text string. Below are 10 examples of how to use the TEXT function, including data and formulas:

1. Format a Number with Commas

Data: 12345.678
Formula:

=TEXT(12345.678, "#,##0.00")

Result: 12,345.68
Explanation: Formats the number with commas and two decimal places.

2. Display a Date in a Specific Format

Data: 01/15/2023 (stored as a date in Excel)
Formula:

=TEXT(A1, "mmmm d, yyyy")

Result: January 15, 2023
Explanation: Converts the date into a full month name, day, and year format.

3. Format a Number as Currency

Data: 1234.56
Formula:

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

Result: $1,234.56
Explanation: Displays the number as currency with a dollar sign and two decimal places.

4. Display Time in 12-Hour Format

Data: 14:30 (stored as time in Excel)
Formula:

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

Result: 02:30 PM
Explanation: Converts the time into a 12-hour format with AM/PM.

5. Add Leading Zeros to a Number

Data: 123
Formula:

=TEXT(123, "00000")

Result: 00123
Explanation: Adds leading zeros to make the number 5 digits long.

6. Combine Text and Formatted Numbers

Data: 1234.56
Formula:

="Total: " & TEXT(1234.56, "$#,##0.00")

Result: Total: $1,234.56
Explanation: Combines static text with a formatted number.

7. Format a Percentage

Data: 0.123
Formula:

=TEXT(0.123, "0.00%")

Result: 12.30%
Explanation: Converts the decimal to a percentage with two decimal places.

8. Display a Fraction

Data: 0.75
Formula:

=TEXT(0.75, "# ?/?")

Result: 3/4
Explanation: Converts the decimal to a fraction.

9. Format a Phone Number

Data: 1234567890
Formula:

=TEXT(1234567890, "(###) ###-####")

Result: (123) 456-7890
Explanation: Formats the number as a phone number.

10. Display a Date as Day of the Week

Data: 01/15/2023 (stored as a date in Excel)
Formula:

=TEXT(A1, "dddd")

Result: Sunday
Explanation: Displays the day of the week for the given date.

Key Notes:

The TEXT function does not change the actual value of the cell; it only changes how the value is displayed.
The format codes used in the TEXT function are the same as those used in custom number formatting in Excel.
If you need to perform calculations, avoid using TEXT as it converts the value to a text string.

Excel Training London

Thursday, 30 January 2025

text