With text in A2:C2, enter =TEXTJOIN(", ",TRUE,A2:C2) in another cell to combine the values with a comma and a space between them. If A2 is North, B2 is East, and C2 is West, the result is North, East, West. This joins the contents without merging or changing the original cells.
What You’ll Need
- An Excel workbook with the values you want to combine, such as text in A2:C2.
- An empty cell for the combined result. Do not put the formula in one of the cells it refers to.
- Excel for Microsoft 365, where TEXTJOIN is available. The formula also works in Excel 2019 and later, including Excel for Mac and Excel for the web.
If you have an older Excel version without TEXTJOIN, you can use the concatenation formula shown in the steps below. Save a copy of your workbook first if you plan to replace or reorganize source data.
Step-by-Step
- Check how your source cells are arranged. Identify the cells to combine and confirm their order. For example, if a row has a first name in A2, a middle name in B2, and a last name in C2, decide whether the result should include every position or skip blank cells. This guide uses cells across one row, but the same approach works for a vertical range such as A2:A4.
- Choose an empty cell for the result. Click a cell outside the source range, such as D2. Keeping the output separate means you can check it before using it elsewhere, and your original values remain available. If the destination cell already contains information, choose a different empty cell so the formula does not overwrite it.
- Enter the TEXTJOIN formula. With A2:C2 containing North, East, and West, type
=TEXTJOIN(", ",TRUE,A2:C2)in D2 and press Enter. The first argument,", ", is the separator: a comma followed by a space. The second argument, TRUE, tells Excel to ignore empty cells. The final argument is the range of values to join. D2 should display North, East, West. - Use a formula for just two cells if you prefer. With North in A2 and East in B2, enter
=A2&", "&B2in C2. The result is North, East. The ampersand joins the pieces, while the quoted comma and space insert the punctuation. This option is useful for two cells or in older Excel versions, but it takes more typing when you have a longer range. - Check what happens with empty cells. With A2 containing North, B2 blank, and C2 containing West,
=TEXTJOIN(", ",TRUE,A2:C2)returns North, West. The blank cell is omitted, so there is no extra comma for it. If you need a placeholder for missing information, add that wording to the source data or build a formula that handles the blank positions deliberately; do not assume TRUE inserts a label. - Fill the formula down for other rows. If each record occupies one row, select D2 and drag its small fill handle at the lower-right corner down the column. Excel adjusts the references for each row, so the next formula will use A3:C3, then A4:C4, and so on. Check a few results near the bottom to make sure the formula has reached every intended row and has not been copied into unrelated records.
- Keep a fixed copy if you no longer want a live formula. The combined result changes when its source cells change. To keep the displayed text as a fixed value, copy the result cells, then use Home > Paste > Paste Values in the destination. Check that the values look right before removing or editing the original source cells. Paste Values replaces formulas with their current results, so those copied results will not update later.
The formula is the same in Excel for Windows, Excel for Mac, and Excel for the web. The location or appearance of ribbon controls can vary slightly between platforms, but the formula itself does not need a Mac-specific or web-specific version.
Common Mistakes
- Choosing Merge & Center instead of joining text. Merge & Center combines worksheet cells into one larger cell; it does not combine all their contents. When multiple selected cells contain data, Excel keeps only the upper-left cell’s content and can discard the rest. Use a formula such as TEXTJOIN when you need the words or values together in a result.
- Leaving out the space after the comma. A delimiter of
","produces North,East. Use", "when you want the more readable North, East. The comma and space are both inside the quotation marks. - Putting the formula inside its own source range. If D2 contains a formula that refers to A2:D2, it includes itself and can create a circular reference. Put the output in a cell outside the range being joined.
- Expecting the formula to change the source cells. TEXTJOIN displays a combined result elsewhere; it does not remove, move, or replace the original values. That is normally safer, but it can be confusing if you expected the source range to become one cell.
- Overlooking formatting and data types. A date or number may join as its underlying value or in a format that is not what you expected. Check the result, especially when combining dates, times, or numbers that need a particular number of decimal places.
Tips
- Use a comma and a space for readable lists. In a cell, North, East, West is easier to scan than North,East,West. You can change the delimiter to another separator, such as a semicolon, by replacing the text between the quotation marks.
- Use a range for neighboring cells. A2:C2 is compact and easy to extend when the values sit next to one another. For selected nonadjacent cells, list each reference separately in TEXTJOIN rather than including unwanted cells between them.
- Keep blank handling intentional. TRUE skips empty cells, which is usually helpful for optional middle names or incomplete records. If blank positions matter, consider whether skipping them could make the result ambiguous before applying the formula to a whole column.
- Leave source values intact until you verify the output. Look for missing words, repeated separators, and unexpected spaces in several rows. Once the result is correct, you can copy and paste values if you need a static list for another workbook or application.
- Remember that formulas recalculate. If you correct a source value, the joined result updates automatically. This is an advantage for working data; for a final export that must not change, paste the results as values after checking them.
Troubleshooting
The formula appears as text instead of returning a result. The cell may be formatted as Text, or an apostrophe may appear before the equals sign. Change the cell format to General, edit the cell, and press Enter to make Excel interpret the entry as a formula. Also make sure there is no space or apostrophe before the opening equals sign.
Excel reports #NAME? Check that TEXTJOIN is spelled correctly and that your Excel version supports it. TEXTJOIN is available in Excel 2019 and later, including Microsoft 365. In an older version, use the ampersand method for a small number of cells. With North in A2 and East in B2, enter =A2&", "&B2 in C2 to return North, East.
The result has no spaces, extra commas, or missing values. Inspect the delimiter in the formula and confirm it is ", ", not just a comma. Then check the source range: a cell outside the range will not be included, and a blank cell is skipped when the second argument is TRUE. If values contain leading or trailing spaces, clean those source entries or account for the spaces before joining.
The formula is rejected after you enter it. In some regional settings, Excel expects semicolons between function arguments rather than commas. If Excel displays a formula error as you enter the formula, use the list separator required by that installation. Keep the delimiter text itself as a quoted comma and space. This is a regional setting difference, not a different TEXTJOIN function.
Dates or numbers look wrong in the combined result. A joined result is text, and the source cell’s displayed number format may not carry across as expected. Format the value as desired in a helper cell or convert it to text in the formula before joining. Check the result against the source data rather than assuming the displayed date or number has been preserved exactly.
Frequently Asked Questions
Does combining cells with a formula delete the original values?
No. A formula places the joined text in the cell where you enter it and leaves the source cells unchanged. The originals are only at risk if you separately delete them or use a destructive action such as merging selected cells that contain multiple values.
How do I add a comma between two cells?
With the values in A2 and B2, enter =TEXTJOIN(", ",TRUE,A2:B2) in another cell. It inserts a comma and space between the values. For exactly two cells, the ampersand formula in the steps is another option.
Can I join cells vertically instead of across a row?
Yes. If the values are in A2:A4, place the formula outside that range and use =TEXTJOIN(", ",TRUE,A2:A4). Excel reads the cells in the range and joins them with the same separator, skipping blanks because TRUE is specified.
Can I use a different separator?
Yes. Replace the delimiter with the text you want inside the quotation marks. For example, a comma and space create a list, while a space, slash, or line-break character may suit other layouts. Check how the result will be used before choosing punctuation, particularly if the text will be imported into another system.
Why is Merge & Center not the right tool?
Merge & Center changes the worksheet layout by turning selected cells into one larger cell. It does not join every selected cell’s contents, and it may retain only the upper-left value. A formula is the appropriate choice when the goal is to display several cell contents together with a comma.
Will the combined result update if I edit a source cell?
Yes, as long as the result is still a formula and calculation is enabled. If you used Paste Values, the result is a fixed text value and will not update. Keep the formula when you want a live result; paste values when you need a stable snapshot.
Related
- How to Remove Duplicates in Excel Safely, Step by Step
- Remove Duplicates in Excel by Entire Row: A Safe Guide
- Remove Duplicates in Excel: A Clear Step-by-Step Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Protect Sheet vs. Protect Workbook in Excel: Key DifferencesIn Excel, Protect Sheet limits what people can change on one worksheet, while Protect Workbook (in the Review…
- How to Add an Image Box to an Excel Data Entry FormTo add an image box to an Excel data-entry form, create or open a VBA UserForm, place an…
- Freeze Panes in Excel: How to Keep Rows and Columns VisibleIn Excel for Microsoft 365 on Windows, use View > Freeze Panes to keep rows or columns visible…
- Power Query Circular Reference: Causes and FixesA Power Query circular reference happens when a query depends on itself, either directly or through other queries,…
- Print Excel Sheets With Gridlines: Settings and FixesYes. In Excel for Microsoft 365 on Windows, select the worksheet, then choose Page Layout > Sheet Options…
- How to Add a Dropdown in Google Sheets: Step-by-StepTo put a dropdown in Google Sheets, select the cell or range, choose Data > Data validation >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.