To concatenate a long list in Excel into one cell, use TEXTJOIN: with your items in A2:A1000, enter =TEXTJOIN(", ",TRUE,A2:A1000) in an empty cell. It joins the values with commas and spaces, while TRUE tells Excel to skip blank cells. Before you start, decide what separator you want and check that the combined result will fit in a single Excel cell.
What You’ll Need
- An Excel workbook, ideally in Microsoft Excel for Microsoft 365 on Windows. TEXTJOIN is also available in Excel for Mac and Excel for the web, though menus and keyboard shortcuts can differ.
- A column or row containing the items you want to combine. The list can contain text, numbers, or a mixture, but review dates and other formatted values before joining them.
- An empty cell for the result. The formula does not overwrite the source list, so you can put the result on the same sheet or a separate one.
- A separator choice, such as a comma and space, a semicolon, or a line break. You can also choose whether blank cells should be skipped.
Step-by-Step
- Check and identify the source list. Find the first and last cells containing the entries you want. For example, if names are in A2:A1000, that is the range to use. Include all the intended entries, but avoid selecting an unnecessarily huge area: a range such as A2:A5000 is usually easier to manage than a whole column. TEXTJOIN can skip blanks inside a selected range, so a few gaps do not require you to reorganize the list first.
- Choose a separator. The separator goes between each pair of joined values. Use a comma followed by a space for a readable sentence-style list, a semicolon if commas already appear within the items, or another short text mark that suits your data. If you want each value on a separate line inside the result cell, use a line break instead; the next step shows that option.
- Select an empty result cell. Click a cell outside the source range, such as B2, and make sure there is room to display the result. You can also use a cell on a separate worksheet. Keeping the output separate from the source prevents the formula from referring to its own result and makes it easier to check the original entries.
- Enter the TEXTJOIN formula. With entries in A2:A6 containing Red, Blue, a blank, Green, and Yellow, enter
=TEXTJOIN(", ",TRUE,A2:A6)in B2. The expected result isRed, Blue, Green, Yellow. The first argument is the separator, the second argument tells Excel whether to ignore empty cells, and the last argument is the list or range to join. In Excel for Microsoft 365, the same formula works on Windows, Mac, and the web. - Confirm the formula. Press Enter, or select the check mark beside the formula bar. If Excel displays the joined text in the result cell, the basic concatenation is complete. If you see the formula itself instead of its result, change the cell format from Text to General, then edit the formula and press Enter again.
- Use a line break between values if that is easier to read. With items in A2:A4 containing North, Central, and South, enter
=TEXTJOIN(CHAR(10),TRUE,A2:A4)in B2. The result contains each region on its own line within B2. To see those lines, select B2 and choose Home > Wrap Text. The line breaks are part of the cell value; they do not put the items into separate worksheet cells. - Adjust the range or delimiter as needed. If new items are added, edit the range in the formula so it includes them. For instance, change A2:A1000 to A2:A1500 if the list grows. You can change the first argument to a different separator without rebuilding the formula. To join a row rather than a column, use the row range, such as A2:F2.
- Check the final output before sharing it. Compare the start and end of the result with the source list and confirm that the separator has not made the combined text confusing. Excel permits up to 32,767 characters in one cell. If your joined result would exceed that limit, it cannot all be stored in one cell; divide the list into smaller groups or keep the values in separate cells instead.
For dates or numbers, remember that Excel may use the stored value rather than the display format you see in the source cell. With dates in A2:A4, for example, use =TEXTJOIN(", ",TRUE,TEXT(A2:A4,"mmm d, yyyy")) to convert them to readable dates before joining. For dates of January 5, February 14, and March 20, 2026, the result is Jan 5, 2026, Feb 14, 2026, Mar 20, 2026. Change the date pattern inside TEXT if you prefer another style.
Common Mistakes
- Using the wrong range. A formula can be correct but still leave out the first or last item if its references are too short. Check the range shown in the formula bar against the actual list, especially after adding new rows.
- Choosing the wrong blank-cell setting. TRUE skips empty cells; FALSE keeps the separator position for empty cells. If blank cells are meant to represent missing entries, FALSE can produce doubled separators or spaces that look like stray punctuation. Use TRUE for a clean list unless those gaps matter.
- Expecting formatting to be preserved. TEXTJOIN combines cell values; it does not carry over each source cell’s font, color, number format, or other styling. Dates may appear as serial numbers, and numbers with leading zeros may lose those zeros if Excel stores them as numeric values. Convert or format values appropriately before joining when their appearance matters.
- Putting the formula inside its own source range. If the formula is entered in a cell included in the range it refers to, Excel can report a circular reference. Keep the result cell outside the list being joined.
- Using a separator that already occurs in the data. If individual entries contain commas, a comma-separated result may be hard to split or interpret later. Choose a less common separator, such as a pipe, or consider leaving the items in separate cells if you need to process them again.
Tips
- Use a line break for compact lists. CHAR(10) separates entries on new lines within one cell. Remember to turn on Wrap Text so those lines are visible. Increase the row height if Excel does not show every line.
- Build in room for future entries. A range that extends beyond the current last item, such as A2:A1000, can accommodate later additions. With TRUE, unused blank cells in that range do not add extra separators.
- Keep the source list intact. TEXTJOIN creates a formula result, not a replacement for the original data. That makes it easy to revise the delimiter, correct a source entry, or rebuild the combined text later.
- Copy the result as a fixed value when needed. Copy the result cell, then use Paste Special > Values to place its current text somewhere without carrying over the formula. The exact Paste Special menu layout can vary between Windows, Mac, and the web.
- Be deliberate with leading or trailing spaces. TEXTJOIN adds only the separator you specify; it does not automatically clean extra spaces inside source entries. If you notice uneven spacing, clean the source data first so the joined result is consistent.
Troubleshooting
- Excel shows #NAME? Check that the function is spelled TEXTJOIN and that your Excel version supports it. TEXTJOIN is available in Microsoft 365 and newer perpetual versions such as Excel 2019, Excel 2021, and Excel 2024. In installations with different regional settings, Excel may use semicolons rather than commas between formula arguments; use the separator your installation expects.
- The result is blank or missing entries. Confirm that the cells in the referenced range contain values and that the range points to the correct column or worksheet. A formula returning an empty string may look blank and will be skipped when the second argument is TRUE. If you want to retain empty positions, change TRUE to FALSE, then check whether the extra separators are useful.
- The result displays as a formula. Select the result cell, choose Home > Number Format, and set it to General. Then press F2 and Enter on Windows to re-enter the formula. On Mac, use Control+U to edit the active cell and press Return; if your keyboard or settings differ, click in the formula bar and confirm the formula there.
- You see #VALUE! or the output is cut off. A single cell cannot hold more than 32,767 characters. Reduce the number of items, shorten their text, or join separate portions into different cells. If the formula returns an error even though the output should be shorter, inspect the source range for unusually long values and verify that the result cell is not part of the range.
- Dates or numbers look different after joining. TEXTJOIN does not reproduce the visible number format of each input cell. Convert those values to text in the formula or create a helper column with the exact display text you want, then join that column. For dates, use a format such as
mmm d, yyyy; for identifiers with leading zeros, store or convert them as text before combining.
Frequently Asked Questions
Can I concatenate more than a few hundred cells?
Yes. TEXTJOIN can process a range containing many cells, so you do not need to write a separate cell reference for every entry. The practical limit is the maximum length of the output cell: 32,767 characters.
Does TEXTJOIN change the original list?
No. It returns a combined value in the cell containing the formula. The source entries remain where they are, and edits to them update the result automatically.
Can I join cells from more than one range?
Yes. TEXTJOIN accepts multiple text arguments, including ranges, though for a long list it is often simpler to put the entries into one continuous range first. Check the output to make sure values are joined in the intended order.
Can I concatenate a list without separators?
Yes. Use an empty text string as the separator. With items in A2:A10, the formula is =TEXTJOIN("",TRUE,A2:A10); the values are joined directly together, with no characters between them.
What is the difference between TEXTJOIN and CONCATENATE?
TEXTJOIN lets you specify one separator for the whole range and choose whether to ignore blank cells. CONCATENATE is an older function that joins specified text arguments but does not offer TEXTJOIN’s convenient delimiter and blank-handling options. For a long list in Microsoft 365, TEXTJOIN is usually the simpler choice.
Can I split the combined list back into separate cells?
In Microsoft 365, Text to Columns is available from Data > Text to Columns for delimiter-based text, and TEXTSPLIT can also divide text with a chosen delimiter. Be cautious if the original entries themselves contain that delimiter, since splitting may not recreate the original list accurately.
Related
- How to Change Strikethrough Color in Excel Cells Easily
- How to Change Note Color in Excel for Easier Scanning
- How to Change a Link Name in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNT vs COUNTIF in Excel: Which One Should You Use?COUNT counts cells containing numbers, while COUNTIF counts cells that meet a condition you specify. Use COUNT for…
- Excel Insert Image from URL: Formula, Steps, and FixesTo insert an image from a URL in Microsoft Excel for Microsoft 365, put the direct image address…
- Average in Excel Between Two Values: Formula & TipsTo average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For…
- COUNTIF in Excel: Count Values Less Than a Set NumberTo count values below a number in Excel, use =COUNTIF(A2:A20,"<10"). Replace A2:A20 with your range and 10 with…
- Excel Formula for the Lowest Value: MIN ExplainedThe Excel formula for the lowest value in a range is =MIN(A1:A10). Replace A1:A10 with the cells you…
- Round Numbers in Word Mail Merge: A Step-by-Step GuideTo round numbers in a Word mail merge, add a numeric picture switch to the merge field, such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.