How to Use the TEXT Function in Excel

The TEXT Function

1. Start with the basic TEXT function structure

• Begin with the TEXT function
• Select the value you want to convert
• Add the format you want inside quotation marks
• Use this structure to turn dates, numbers, and times into readable text formats

2. Format a full date in a different layout

• Use TEXT on a date cell
• Apply a format pattern that rearranges the month, day, and year
• In the video, this was used to flip the date into a more readable custom format
• Rename the result something like “Formatted Date” so it is easy to identify

3. Show only the month and year

• Use TEXT again on the same date field
• Apply a shorter date pattern that returns the abbreviated month and full year
• This is useful for monthly reporting, labels, and dashboard headings
• The video also showed how changing the number of month characters changes the output from abbreviated month to full month name

4. Pull out just the month from a date

• Add a separate column for month
• Use TEXT to return the month name from the date
• Use a shorter month format for the abbreviated version
• Use a longer month format for the full month name
• This is useful when grouping service calls, invoices, or customer activity by month

5. Pull out just the year from a date

• Add a separate year column
• Use TEXT to return the year as text
• The video pointed out that this matters when you later use lookups or counting functions
• Since the result is text, make sure you are matching it to text values in any downstream formulas

6. Pull out the day of the week

• Add a day column
• Use TEXT on the date field to return the weekday name
• The longer day format returns the full weekday
• The shorter day format returns the abbreviated weekday
• This is especially useful for weekday-based analysis, such as tracking customer demand by day of the week

7. Create structured work order or invoice IDs

• Use TEXT to format a numeric value into a fixed-length identifier
• In the video, this was used to create a work order style number with a prefix
• This approach is helpful for job numbers, invoice IDs, ticket numbers, and other structured labels
• The video also showed that you can build similar IDs by combining text strings with the ampersand operator

8. Format numbers as currency text

• Use TEXT on a numeric value
• Apply a currency-style format so the result displays like a formatted dollar amount
• The video showed versions with and without decimals
• This is useful when you need formatted currency inside a sentence, label, or exported text field
• Keep in mind that once formatted with TEXT, the result becomes text instead of a number

9. Format hours from time values

• Use TEXT on a time field
• Apply a time pattern to return the hour in the format you need
• In the video, this was used to display values like 9 and 14 from time-based cells
• This can be helpful when creating hourly service summaries or time-based reporting categories

10. Review custom formatting options for more ideas

• Open the cell formatting menu with Ctrl + 1
• Go to Custom formatting
• Review the available patterns for dates, times, and number displays
• The video used this section to show additional formatting possibilities that can also be applied through the TEXT function

11. Use the TEXT function where formatting needs to travel with the result

• Use TEXT when the formatted version needs to become the actual output
• This is especially useful for labels, report text, IDs, and date-based grouping fields
• The examples in the video focused on practical reporting uses rather than just changing cell appearance