TEXT Function in Excel: Complete Tutorial with Examples

Need to format numbers, dates, or times as text? TEXT converts values to text with specific formatting, making it perfect for creating custom displays, combining formatted numbers with text, and controlling how data appears. It’s the formatting powerhouse of Excel.

What Makes TEXT Useful

TEXT gives you complete control over formatting:

• Custom number formats – Display numbers exactly how you want
• Date formatting – Convert dates to any text format
• Time formatting – Show times in custom styles
• Combine with text – Merge formatted values into sentences
• Leading zeros – Preserve zeros that disappear with plain numbers

The Syntax

=TEXT(value, format_text)

Value is the number or date to format. Format_text is the formatting code in quotes.

Example 1: Format Numbers with Commas

Display numbers with thousand separators:

Formula: =TEXT(A2, "#,##0")

Result in B2: 1,234,567

Result in B3: 9,876,543

Adds commas automatically at thousand positions.

Example 2: Format Currency

Display numbers as currency with dollar signs:

Formula: =TEXT(A2, "$#,##0.00")

Result in B2: $1,234.56

Result in B3: $9,999.99

Result in B4: $500.00

Always shows two decimal places.

Example 3: Format Dates as Text

Convert dates to readable text:

Formula: =TEXT(A2, "MMMM DD, YYYY")

Result in B2: January 15, 2025

Result in B3: March 22, 2025

Result in B4: December 05, 2025

Example 4: Day of Week from Date

Show which day of the week:

Formula: =TEXT(A2, "DDDD")

Result in B2: Wednesday

Result in B3: Saturday

Full day name from any date.

Example 5: Leading Zeros for Codes

Preserve leading zeros in numbers:

Formula: =TEXT(A2, "00000")

Result in B2: 00001

Result in B3: 00025

Result in B4: 00123

Creates 5-digit codes with leading zeros.

Example 6: Percentages

Format decimals as percentages:

Formula: =TEXT(A2, "0%")

Result in B2: 16%

Result in B3: 95%

Formula for decimals: =TEXT(A2, "0.00%")

Result for 0.155: 15.50%

Common Format Codes

Number Formats

Date Formats

Time Formats

Example 7: Combine Text with Formatted Numbers

Create sentences with formatted values:

Formula: ="Total sales: " & TEXT(A2, "$#,##0")

Result in B2: Total sales: $45,678

Result in B3: Total sales: $12,345

Example 8: Format Dates in Sentences

Include formatted dates in text:

Formula: ="Meeting on " & TEXT(A2, "MMMM D, YYYY") & " at 2pm"

Result: Meeting on March 15, 2025 at 2pm

Example 9: Custom Date Format

Create custom date displays:

Formula: =TEXT(A2, "DDD, MMM D")

Result: Wed, Jan 15

Short day, short month, day number.

Example 10: Phone Number Formatting

Format 10-digit numbers as phone numbers:

Formula: =TEXT(A2, "(000) 000-0000")

Result in B2: (555) 123-4567

Result in B3: (415) 987-6543

Example 11: Fiscal Year from Date

Display fiscal year format:

Formula: ="FY" & TEXT(A2, "YY")

Result in B2: FY25

Result in B3: FY24

Example 12: Time Duration

Format hours and minutes:

Formula: =TEXT(A2/24, "H:MM")

Result in B2: 1:30

Result in B3: 2:45

Converts decimal hours to H:MM format.

Advanced Format Codes

Conditional formatting:

[Blue]#,##0;[Red]-#,##0

Positive numbers blue, negative red.

Text in format:

"Order "#0000

Includes literal text.

Fractions:

# ?/?

Displays as fraction.

Scientific notation:

0.00E+00

Scientific format.

Common Mistakes to Avoid

TEXT Returns Text, Not Numbers

• Can’t do math with TEXT results
• Use original value for calculations
• TEXT is for display only

Date Format Codes Are Case-Sensitive

• MMMM = full month name
• mmmm = minutes (wrong!)
• YYYY = four-digit year
• Always use uppercase for months and years

Missing Quotes

• Format codes must be in quotes
• TEXT(A1, "#,##0") is correct
• TEXT(A1, #,##0) causes error

Error Handling

Handle non-dates:

=IF(ISNUMBER(A2), TEXT(A2, "#,##0"), "Not a number")

Check for valid dates:

=IF(AND(A2<>"", ISNUMBER(A2)), TEXT(A2, "MM/DD/YYYY"), "Invalid")

Default for empty:

=IF(A2="", "", TEXT(A2, "$#,##0.00"))

Practical Applications

Invoice formatting:

="Invoice #" & TEXT(A2, "0000") & " dated " & TEXT(B2, "MM/DD/YYYY")

Report headers:

="Sales Report for " & TEXT(A2, "MMMM YYYY")

File naming:

="Report_" & TEXT(TODAY(), "YYYY-MM-DD") & ".xlsx"

Combining TEXT with Other Functions

TEXT + CONCATENATE:

=CONCATENATE("$", TEXT(A2, "#,##0"))

TEXT + IF:

=IF(A2>1000, TEXT(A2, "$#,##0"), TEXT(A2, "$0"))

TEXT + TODAY:

=TEXT(TODAY(), "DDDD, MMMM D, YYYY")

Tips for Using TEXT

Test format codes Try different codes to get desired output.

Use for display only Keep original values for calculations.

Combine with concatenation Build complex formatted strings.

Reference format code cells Store format codes in cells for easy changes.

When to Use TEXT

Use TEXT when:

• Need specific number or date formatting
• Combining formatted values with text
• Preserving leading zeros
• Creating custom displays
• Building dynamic text strings

Don’t use TEXT when:

• Default formatting works fine
• Need to calculate with values
• Working with actual dates/numbers (not text)

Start Using It

Pick a number or date. Decide how you want it formatted. Type =TEXT, reference the cell, and add a format code in quotes. Experiment with different codes to see various formats.

TEXT gives you unlimited formatting control.

Questions about TEXT? Need help with specific format codes? Let’s connect.