Why does my formula show an error?

Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.

Should I copy formulas down or use a Table?

Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.

Formulas - 2026-01-21

TEXT vs TEXTJOIN: Formatting and Combining Text

TEXT formats numbers, TEXTJOIN combines text. Learn when to use each with practical examples.

TEXT vs TEXTJOIN: Complete Guide

These functions sound similar but do completely different things. TEXT formats numbers as text, while TEXTJOIN combines multiple text values. See Excel Functions Compared for more comparisons.

Quick Comparison

Function Purpose Excel Version TEXT Format numbers/dates as text All versions TEXTJOIN Combine multiple values with delimiter 2019+ / 365

The TEXT Function

Converts numbers and dates to formatted text strings. Syntax: \\\ =TEXT(value, format_text) \\\ Number Formatting Examples \\\ =TEXT(1234.5, "$#,##0.00") → "$1,234.50" =TEXT(0.25, "0%") → "25%" =TEXT(1234567, "#,##0") → "1,234,567" =TEXT(0.5, "0.00%") → "50.00%" =TEXT(123, "000000") → "000123" \\\ Date Formatting Examples \\\ =TEXT(TODAY(), "MMMM DD, YYYY") → "January 21, 2024" =TEXT(TODAY(), "MM/DD/YY") → "01/21/24" =TEXT(TODAY(), "DDDD") → "Wednesday" =TEXT(NOW(), "HH:MM AM/PM") → "02:30 PM" =TEXT(TODAY(), "YYYY-MM-DD") → "2024-01-21" \\\ Common Format Codes Code Meaning Example 0 Digit (shows zeros) 001, 002 # Digit (hides zeros) 1, 2 , Thousands separator 1,000 . Decimal point 1.50 % Percentage 50% $ Currency $100 YYYY 4-digit year 2024 MM 2-digit month 01 DD 2-digit day 21 DDDD Full day name Monday MMMM Full month name January

The TEXTJOIN Function

Combines text from multiple cells with a delimiter between them. Syntax: \\\ =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) \\\ delimiter: Character(s) between values ignore_empty: TRUE to skip blanks, FALSE to include text1, text2...: Values to combine Basic Examples \\\ =TEXTJOIN(", ", TRUE, "Apple", "Banana", "Cherry") → "Apple, Banana, Cherry" =TEXTJOIN(" ", TRUE, A1:A5) → "John Paul George Ringo Pete" =TEXTJOIN("-", TRUE, "2024", "01", "21") → "2024-01-21" \\\ With Range References \\\ =TEXTJOIN(", ", TRUE, A1:A10) \\\ Combines all non-empty cells in A1:A10 with commas. Ignore Empty Parameter \\\ ' A1="Apple", A2="", A3="Cherry" =TEXTJOIN(", ", TRUE, A1:A3) → "Apple, Cherry" =TEXTJOIN(", ", FALSE, A1:A3) → "Apple, , Cherry" \\\

Real-World Examples

Example 1: Create Full Address \\\ =TEXTJOIN(", ", TRUE, Street, City, State, Zip) → "123 Main St, New York, NY, 10001" \\\ Example 2: Format Phone Number \\\ =TEXT(5551234567, "(###) ###-####") → "(555) 123-4567" \\\ Example 3: Create Email List \\\ =TEXTJOIN("; ", TRUE, EmailColumn) → "[email protected]; [email protected]; [email protected]" \\\ Example 4: Format Currency for Report \\\ ="Total: "&TEXT(SUM(A1:A10), "$#,##0.00") → "Total: $1,234.56" \\\ Example 5: Create CSV Line \\\ =TEXTJOIN(",", FALSE, A1:E1) → "Name,Date,Amount,Status,Notes" \\\

Before TEXTJOIN: CONCATENATE

Before Excel 2019, we used CONCATENATE or &: \\\ ' Old way =A1&", "&A2&", "&A3 ' Or =CONCATENATE(A1, ", ", A2, ", ", A3) ' New way (much better!) =TEXTJOIN(", ", TRUE, A1:A3) \\\ TEXTJOIN advantages: Works with ranges Handles empty cells Much cleaner syntax

Combining TEXT and TEXTJOIN

Create formatted strings from data: \\\ ="Order #"&TEXT(OrderNum, "00000")&" - "&TEXT(OrderDate, "MMM DD, YYYY")&" - "&TEXT(Amount, "$#,##0.00") → "Order #00123 - Jan 21, 2024 - $1,234.56" \\\

Common Mistakes

TEXT Mistakes Wrong: Using wrong format codes \\\ =TEXT(0.5, "percent") → "percent" (literal text!) =TEXT(0.5, "0%") → "50%" (correct) \\\ Wrong: Forgetting quotes \\\ =TEXT(A1, $#,##0) → Error =TEXT(A1, "$#,##0") → Correct \\\ TEXTJOIN Mistakes Wrong: Forgetting ignore_empty \\\ =TEXTJOIN(", ", A1:A5) → Error (missing parameter) =TEXTJOIN(", ", TRUE, A1:A5) → Correct \\\

When to Use Each

Scenario Use Format a number as currency TEXT Format a date TEXT Add leading zeros TEXT Combine names into one cell TEXTJOIN Create comma-separated list TEXTJOIN Build addresses TEXTJOIN Create dynamic labels Both together

Excel Functions Compared LEFT vs MID vs RIGHT Text Functions Data Cleaning Tips

Formula debugging workflow

Step Action 1 Select the cell and read the formula in the formula bar 2 Use Formulas → Evaluate Formula for nested functions 3 Check for text stored as numbers (green triangle) 4 Fix errors using our formula error guide Go deeper: Compare similar functions · Fix common problems

Frequently asked questions

Why does my formula show an error? Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.
Should I copy formulas down or use a Table? Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.