Display Excel Text and Formulas Together: Master TEXT, TEXTJOIN, and Dynamic Arrays
When you need to display Excel text and formulas together in a report, the safest approach is to first lock in number or date formatting with TEXT(), then combine the values using & or TEXTJOIN. Use the examples below to handle blanks, delimiters, and line breaks.
Quick Fix (3 Minutes)
="Sales are " & TEXT(B2,"#,##0") & " won, and the growth rate is " & TEXT(C2,"0.0%") & "."Key Principles
- & concatenation converts values to text, so use
TEXT()to preserve formatting. - TEXTJOIN keeps results clean with delimiter and ignore-empty options.
- For line breaks, use
CHAR(10)and turn on Wrap Text for the cell.
Example 1: Natural-Language Dates and Quarters
=TEXT(A2,"yyyy-mm-dd") & " delivery completed"
="Q" & ROUNDUP(MONTH(A2)/3,0) & " (quarter)"
=TEXT(A2,"yyyy mmm") & " order status"Example 2: Amounts, Percentages, and Units
="Total: " & TEXT(D2,"#,##0 won")
="Growth rate: " & TEXT(E2,"0.0%")
=TEXT(F2,"0.0") & " kg shipped"Example 3: CONCAT and TEXTJOIN
=TEXTJOIN(", ", TRUE, FILTER(Table1[Item], Table1[Order Number]=H2))=CONCAT("Customer: ", A2, " / Grade: ", B2)Example 4: Automatic Summary Sentence (Dynamic Array)
=LET(rows, FILTER(B2:B100, C2:C100="Delayed"), IFERROR("Delayed items: " & TEXTJOIN(", ", TRUE, rows), "No delays"))Example 5: Line Breaks and Special Characters
="Total: " & TEXT(D2,"#,##0") & CHAR(10) & "Contact: " & E2
"Search term: ""*order*"""
"Formula: ""=VLOOKUP("" & A2 & "")"""Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Number formatting disappears | Text conversion when using & concatenation | Use TEXT(value,”format”) |
| Date displays as 45123 | Serial number is displayed | Use TEXT(date,”yyyy-mm-dd”) |
| Duplicate delimiters | Blank values are included | Use TEXTJOIN(“, “,TRUE,range) or IF(LEN(…)) |
| Line break does not appear | Cell option is not enabled | Format Cells → enable Wrap Text |
| Quotation marks do not display | Missing escape characters | Use two double quotation marks |
Related Posts
- Data Cleanup: TRIM and CLEAN
- Table Basics and Benefits
- SUMIFS Multiple-Criteria Best Practices
- XLOOKUP Basics and How to Switch
- Complete Guide to Advanced Filter