To join the values in cells A2:A10 with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A10). The formula skips empty cells, so the result contains clean separators between the nonblank values.
What TEXTJOIN Does
TEXTJOIN combines text from multiple cells or text values into one text string. Its first argument is the delimiter: the text Excel inserts between each item. In this example, the delimiter is a comma followed by a space, which produces a readable result such as Red, Blue, Green. Use a comma without a following space if you need a compact result, or choose another delimiter such as a semicolon or line break.
The second argument tells Excel whether to ignore empty values. Set it to TRUE to omit blank cells and avoid doubled commas; set it to FALSE when empty positions should remain represented by delimiters. The remaining arguments specify the text or ranges to combine. You can provide one range, several ranges, or individual text values. TEXTJOIN is available in Microsoft 365 and Excel 2019 or later, including current Excel for Mac and Excel for the web.
With names in A2:A10, for example, the formula returns one comma-separated list. If A2 contains Ana, A3 is blank, and A4 contains Luis, the result with TRUE is Ana, Luis, not Ana, , Luis. This makes the function useful for building lists from columns that may contain gaps.
How to Join Cells Step by Step
For a basic list, enter the formula in the cell where you want the combined text to appear. Keep the source values in their original cells; TEXTJOIN creates a result and does not replace or rearrange those values.
- Choose an empty result cell, such as B2.
- Type a formula that names the delimiter, whether blanks should be ignored, and the source range.
- Press Enter. Review the result and adjust the range if it leaves out a value or includes an unwanted heading.
- If you need the result for more rows, copy the formula and update its references as appropriate.
With city names in A2:A5, enter =TEXTJOIN(", ",TRUE,A2:A5) in B2. If the cells contain Boston, Chicago, Denver, and Miami, the result is Boston, Chicago, Denver, Miami. If the source range might grow, you can initially select more rows, but remember that a large range can include unrelated data if values are later added below it.
In Microsoft 365 for Windows, enter the formula directly in the cell or in the formula bar and press Enter. The same formula syntax works in current Excel for Mac and Excel for the web. Ribbon locations can differ among platforms, but there is no special command required to use TEXTJOIN.
Joining Separate Ranges and Adding Labels
TEXTJOIN can combine more than one range in a single result. This is handy when related values are separated by other columns or when a label needs to appear alongside a cell value. Each range or value is another argument, and the same delimiter is placed between successive nonempty items.
With first names in A2:A4 and last names in C2:C4, =TEXTJOIN(", ",TRUE,A2:A4,C2:C4) returns one comma-separated sequence containing the names from both ranges. It does not pair A2 with C2 to create a full name; it processes the first range and then the second. To combine each person’s first and last name into individual full names, create a row-level formula in another column first, then join that helper column.
With a product name in A2 and a category in B2, a formula can include fixed text as well as cell references. For instance, =TEXTJOIN(" ",TRUE,A2,"-",B2) puts a space between each item, but because the hyphen is its own item, the result includes spaces around it. If exact punctuation matters, choose the delimiter and text arguments accordingly; direct concatenation with the ampersand operator can be clearer for a one-off phrase.
Handling Blank Cells, Spaces, and Errors
TRUE in the second argument skips genuinely empty cells and cells whose formulas return an empty string. It does not automatically remove cells that contain one or more spaces. A cell that looks blank may therefore still create an extra separator if it contains invisible spaces. Clean such source values before joining them, for example by using Excel’s TRIM function in a helper column, then join the cleaned results.
With source text in A2:A10, use =TRIM(A2) in B2 and fill the formula down to B10 to remove leading and trailing spaces and reduce repeated internal spaces. Then, with the cleaned values in B2:B10, use =TEXTJOIN(", ",TRUE,B2:B10) in C2. TRIM removes ordinary space characters; it may not remove every kind of nonbreaking or imported whitespace, which can require additional cleanup.
TEXTJOIN does not repair error values in the source range. If one of the cells contains an error such as #N/A, the combined result may return that error too. Find and correct the source error, or handle it deliberately in a helper formula if the error should be replaced or omitted. Avoid hiding errors indiscriminately: a missing or invalid source value may be important information rather than harmless clutter.
Commas, Dates, Numbers, and Line Breaks
The delimiter is ordinary text, so it can contain punctuation, spaces, or other characters. A comma followed by one space is a common choice for names, tags, and short lists. If the values themselves may contain commas, consider a different delimiter or a format that makes the fields unambiguous. A plain joined string does not automatically quote values or escape punctuation as a CSV export would.
Numbers and dates need special attention because a joined result is text, not a numeric calculation. Excel may convert a referenced number or date to text in a way that does not match the cell’s displayed number format. If a date should appear as a friendly label, format it explicitly inside the TEXTJOIN arguments. With dates in A2 and A3, =TEXTJOIN(", ",TRUE,TEXT(A2,"mmm d, yyyy"),TEXT(A3,"mmm d, yyyy")) produces a result such as Nov 2, 2026, Nov 5, 2026 when those are the underlying dates. Choose a format string that fits your audience and date convention.
A line break can be used as the delimiter when you want each item on its own line within a single cell. With entries in A2:A10, =TEXTJOIN(CHAR(10),TRUE,A2:A10) joins them with line breaks. Turn on Wrap Text for the result cell from Home > Wrap Text so the breaks are visible as separate lines. This is useful for compact notes, although the output is still one cell and may be less convenient to sort or filter than the original rows.
When to Use TEXTJOIN, CONCAT, or Ampersand
TEXTJOIN is usually the clearest choice when the same delimiter must be inserted repeatedly, especially for a range with possible blanks. CONCAT can combine text and ranges, but it does not provide a delimiter argument or an option to ignore blank cells. You would need to add separators separately, making a variable-length list harder to manage.
The ampersand operator joins a small, fixed number of pieces and gives precise control over punctuation. With a first name in A2 and a last name in B2, =A2&" "&B2 combines them with one space. That is often preferable for a two-part name, while TEXTJOIN is more concise for a long list. CONCATENATE is an older function that remains available for compatibility, but Microsoft recommends CONCAT for that style of joining; neither function replaces TEXTJOIN’s delimiter and blank-handling options.
- Use TEXTJOIN for a delimited list from a range, particularly when blank cells should be skipped.
- Use the ampersand for a short, fixed expression where you want to place each separator exactly.
- Use CONCAT when you need to combine values or ranges without a repeated delimiter.
Common Formula Problems and Fixes
If Excel reports a formula problem as soon as you enter it, check the argument separators. The examples here use commas between arguments, as requested for the standard Excel syntax. Some regional settings use semicolons in formulas instead. In that case, replace the argument-separating commas with semicolons, but keep the comma inside the quoted delimiter text unchanged.
If the result contains repeated commas, check whether the relevant cells are truly empty or contain spaces. TRUE skips empty values, not space-only text. If the output stops before expected values, make sure the selected range includes them and that there is no unhandled error. If the output shows a date as a number, apply TEXT with an appropriate date format, as shown above.
A long list can also exceed Excel’s cell text limit. A cell can contain at most 32,767 characters, so TEXTJOIN cannot return an arbitrarily large combined string. If a formula returns a value error for a very large range, check the expected result length and consider keeping the data in separate rows, splitting it into smaller groups, or exporting it in a structured form rather than forcing it into one cell.
Frequently Asked Questions
How do I join text in Excel with a comma and a space?
With the values in A2:A10, enter =TEXTJOIN(", ",TRUE,A2:A10). The delimiter is a comma and a space, and TRUE tells Excel to skip empty cells.
How do I join cells with commas but no spaces?
With the values in A2:A10, use =TEXTJOIN(",",TRUE,A2:A10). The result will look like Red,Blue,Green rather than Red, Blue, Green.
Can TEXTJOIN skip blank cells?
Yes. Set its second argument to TRUE. It skips empty cells and formula results that are empty strings, but not cells containing spaces or other invisible characters.
Can I join text from multiple columns?
Yes. Put each range in the formula as a separate argument. Excel processes the ranges in the order you give them; it does not automatically combine corresponding cells into row-by-row records.
Why does my Excel formula use semicolons instead of commas?
Excel’s argument separator depends on regional settings. If your installation expects semicolons, use them between function arguments. A comma that is inside quotation marks as part of the delimiter remains a comma.
Does TEXTJOIN change the original cells?
No. It returns a combined text value in the formula cell and leaves the source cells unchanged. To keep a static copy, copy the result and use Paste Special > Values.
Keep the Joined Result Useful
A comma-separated string is convenient for display, copying, or creating a compact summary, but it is not a substitute for structured data. Once several values are joined into one cell, Excel cannot filter or sort each original item independently without splitting the text again. Keep the source values in separate cells when they will need analysis, lookup, or editing later.
For a quick list, TEXTJOIN provides a readable formula with one place to define the separator and a clear setting for blank cells. Confirm the source range, decide whether blank-looking values need cleanup, and check how numbers or dates should appear. Those small choices usually determine whether the result is ready to use or needs a little more preparation.
Related
- Excel Not Equal: The <> Operator Explained
- Excel Arrow Keys Not Moving Between Cells? Quick Fixes
- Excel Not Responding? Causes, Fixes, and Recovery Steps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Named Range Sort: Sort Data or Name DefinitionsIn Excel, a named range is a label for a cell or group of cells, not a separate…
- Power Query Sort: How to Sort and Keep Rows in OrderTo sort data in Power Query in Excel for Microsoft 365 on Windows, open the query, select the…
- Excel Formula for Number of Days Between Two DatesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- SUMIF in Excel: Practical Examples and FormulasIn Excel for Microsoft 365, SUMIF adds values that meet one condition. Its syntax is =SUMIF(range, criteria, [sum_range]);…
- Excel Formula for Percentage of Total: Clear ExamplesTo calculate a percentage of a total in Excel, divide the part by the total: if the value…
- Excel Sum Across Sheets: Formulas and 3D ReferencesTo sum the same cell across consecutive worksheets in Excel, use a 3D reference such as =SUM(Jan:Mar!B2). If…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.