How to Use TEXT in Excel
The TEXT function in Excel transforms values into formatted text strings. The syntax is straightforward: `=TEXT(value, format_text)`. The first argument is the value you want to format—a number,...
Key Insights
- The TEXT function converts values into formatted text strings using format codes, making it essential for concatenating numbers, dates, and times with other text while preserving their appearance.
- Format codes use specific patterns like “#,##0.00” for numbers and “yyyy-mm-dd” for dates, giving you precise control over how values display when combined with text strings.
- TEXT always returns text, not numbers, which means formatted results won’t work in calculations unless converted back—understanding this distinction prevents common formula errors.
Understanding the TEXT Function Syntax
The TEXT function in Excel transforms values into formatted text strings. The syntax is straightforward: =TEXT(value, format_text). The first argument is the value you want to format—a number, date, or cell reference. The second argument is a text string containing format codes that define how the value should appear.
Here’s a basic example:
=TEXT(1234.5, "0.00")
This returns “1234.50” as text. The format code “0.00” specifies that the number should display with exactly two decimal places.
The TEXT function becomes indispensable when you need to combine formatted values with other text. Standard cell formatting won’t help when concatenating cells because Excel drops the formatting. For instance, if cell A1 contains 1234.5 formatted as currency, the formula ="Total: "&A1 displays “Total: 1234.5” instead of “Total: $1,234.50”. The TEXT function solves this problem.
Formatting Numbers with TEXT
Number formatting with TEXT gives you complete control over how numeric values appear when converted to text strings. The format codes use specific characters: “0” forces a digit to display (showing zero if none exists), “#” displays a digit only if present, and “,” adds thousands separators.
For currency formatting:
=TEXT(1234.56, "$#,##0.00")
This returns “$1,234.56”. The dollar sign appears literally, the hash marks and zeros define digit positions, the comma adds the thousands separator, and “.00” ensures two decimal places always show.
For different currencies or symbols:
=TEXT(1234.56, "€#,##0.00")
=TEXT(1234.56, "£#,##0.00")
=TEXT(1234.56, "#,##0.00 USD")
Percentage formatting converts decimal values:
=TEXT(0.85, "0.00%")
This returns “85.00%”. Excel multiplies the decimal by 100 and adds the percent symbol. The format “0%” would show “85%” without decimal places.
You can create conditional formats with colors and conditions:
=TEXT(A1, "[Red][<0]$#,##0.00;[Green]$#,##0.00")
This displays negative numbers in red and positive numbers in green. The semicolon separates format conditions.
For scientific notation:
=TEXT(12345, "0.00E+00")
This returns “1.23E+04”.
Date and Time Formatting
Date and time formatting with TEXT uses letter codes that represent different components. Understanding these codes unlocks powerful date display options.
Common date format codes:
- “d” or “dd” - day (1 or 01)
- “ddd” or “dddd” - day name (Mon or Monday)
- “m” or “mm” - month number (1 or 01)
- “mmm” or “mmmm” - month name (Jan or January)
- “yy” or “yyyy” - year (23 or 2023)
For a full date display:
=TEXT(TODAY(), "dddd, mmmm dd, yyyy")
This might return “Wednesday, March 15, 2023”. The TODAY() function provides the current date, and TEXT formats it into a readable string.
For shorter date formats:
=TEXT(A1, "mm/dd/yyyy")
=TEXT(A1, "dd-mmm-yy")
=TEXT(A1, "yyyy-mm-dd")
These return formats like “03/15/2023”, “15-Mar-23”, and “2023-03-15” respectively. The last format is particularly useful for sorting and file naming.
Time format codes:
- “h” or “hh” - hours (1 or 01)
- “m” or “mm” - minutes (1 or 01)
- “s” or “ss” - seconds (1 or 01)
- “AM/PM” - displays time in 12-hour format
For time formatting:
=TEXT(NOW(), "hh:mm AM/PM")
=TEXT(NOW(), "hh:mm:ss")
The first returns “02:30 PM” while the second shows “14:30:45” in 24-hour format.
Combining date and time:
=TEXT(NOW(), "yyyy-mm-dd hh:mm:ss")
This produces timestamp formats like “2023-03-15 14:30:45”, perfect for logging or database entries.
Practical Applications and Combinations
The TEXT function’s real power emerges when combining formatted values with other text strings. This is where concatenation becomes essential.
Creating dynamic file names:
="Invoice_"&TEXT(TODAY(),"yyyymmdd")&"_"&A1
If A1 contains a customer ID like “C1234”, this generates “Invoice_20230315_C1234”. This pattern ensures chronological sorting and unique identifiers.
Building dynamic labels and headers:
="Total Sales: "&TEXT(SUM(B2:B100),"$#,##0.00")
="Report Generated: "&TEXT(NOW(),"mmmm dd, yyyy at hh:mm AM/PM")
These create professional-looking text that updates automatically with your data.
For complex concatenations:
="Order #"&A1&" placed on "&TEXT(B1,"mmm dd, yyyy")&" for "&TEXT(C1,"$#,##0.00")
If A1=“12345”, B1 contains March 15, 2023, and C1=1250.50, this returns: “Order #12345 placed on Mar 15, 2023 for $1,250.50”.
Creating address blocks or multi-line text:
=A1&CHAR(10)&B1&", "&C1&" "&TEXT(D1,"00000")
The CHAR(10) inserts a line break (when wrap text is enabled). If D1 contains a ZIP code like 1234, TEXT formats it as “01234”, preserving leading zeros.
Common Pitfalls and Troubleshooting
The most critical issue with TEXT is that it always returns a text string, not a number. This creates problems when you need to use the result in calculations.
Consider this example:
=A1*2 // Returns 2468 if A1=1234
=TEXT(A1,"0")*2 // Returns 2468 (Excel converts back to number)
=TEXT(A1,"0")&TEXT(A1,"0") // Returns "12341234" (text concatenation)
If you need to perform calculations on TEXT results, wrap them in VALUE():
=VALUE(TEXT(A1,"0.00"))*2
However, this defeats the purpose of TEXT. Use TEXT only for display purposes, not for values that need further calculation.
Regional format differences cause confusion. Date format codes may interpret differently based on your Excel locale. The format “mm/dd/yyyy” is standard in the US, but many regions use “dd/mm/yyyy”. Test your formulas if sharing workbooks internationally.
Invalid format codes return “#VALUE!” errors:
=TEXT(1234, "xyz") // Error: invalid format code
=TEXT(A1, "") // Error: empty format string
Always validate format codes. Excel’s custom number format dialog can help you build and test format strings before using them in TEXT functions.
Leading zeros disappear without proper formatting:
=TEXT(123, "0") // Returns "123"
=TEXT(123, "00000") // Returns "00123"
This is crucial for ZIP codes, product IDs, or any identifier that requires fixed-width formatting.
Best Practices and Tips
Use TEXT when you need to combine formatted values with text strings. Use cell formatting when you’re just displaying values in cells without concatenation. Cell formatting is more efficient and maintains the underlying numeric value for calculations.
Performance considerations: TEXT functions recalculate whenever the worksheet changes. In large workbooks with thousands of TEXT formulas, this can slow performance. Consider using TEXT only where necessary and using cell formatting for simple display needs.
Here’s a reference table of the most useful format codes:
// Numbers
"0" // 1234
"0.00" // 1234.56
"#,##0" // 1,234
"$#,##0.00" // $1,234.56
"0%" // 85%
"0.00%" // 85.23%
// Dates
"yyyy-mm-dd" // 2023-03-15
"mm/dd/yyyy" // 03/15/2023
"dddd, mmmm dd" // Wednesday, March 15
"mmm dd" // Mar 15
"yyyymmdd" // 20230315 (for sorting/file names)
// Times
"hh:mm" // 14:30
"hh:mm AM/PM" // 02:30 PM
"hh:mm:ss" // 14:30:45
// Combined
"yyyy-mm-dd hh:mm:ss" // 2023-03-15 14:30:45
Create a reference sheet in your workbook with these format codes and examples. This saves time and reduces errors when building complex formulas.
For repeating patterns, consider using named ranges or cell references for format codes:
=TEXT(A1, FormatCodes!$A$1)
This approach makes updating formats across multiple formulas easier and more maintainable.
The TEXT function is one of Excel’s most versatile tools for creating professional, readable output that combines data with descriptive text. Master these format codes, understand the text-versus-number distinction, and you’ll be able to build sophisticated reports and dynamic labels that make your spreadsheets more useful and professional.