Summary

To join multiple cell values with a comma, you can use the TEXTJOIN function. In the example shown, the formula in H5, copied down, is:

=TEXTJOIN(", ",TRUE,B5:F5)

TEXTJOIN joins the values in B5:F5 with a comma and a space between each one, and skips empty cells. TEXTJOIN is available in Excel 2019+ and Excel 365. In Excel 2016 and older, you can use a formula based on the SUBSTITUTE and TRIM functions:

=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5&" "&F5)," ",", ")

See below for details and one limitation of this formula.

Generic formula

=TEXTJOIN(", ",TRUE,range)

Explanation

In this example, the goal is to join the values in columns B through F into one cell, with a comma and a space between each value. Some cells are empty, and an empty cell should not add an extra comma.

The TEXTJOIN function

The core of this formula is the TEXTJOIN function, which is designed to join values with a delimiter. TEXTJOIN takes three arguments: the delimiter, ignore_empty, and the values to join, which can be a range:

=TEXTJOIN(", ",TRUE,B5:F5)

The delimiter is a comma followed by a space (", "). Ignore_empty is TRUE, so TEXTJOIN skips the empty cells in the range. In row 5, only B5 and D5 hold values, and the result is "figs, apples". With FALSE, TEXTJOIN would keep the empty cells and add a delimiter for each one: "figs, , apples, , ". The concatenation operator (&) has the same problem: =B5&", "&C5&", "&D5&", "&E5&", "&F5 adds a comma for every cell, empty or not.

To join cells with another delimiter, change the first argument. For example, to join cells with a forward slash:

=TEXTJOIN("/",TRUE,B8:F8) // returns "dates/star fruit/pears/pears/limes"

To put each value on its own line, use CHAR(10) as the delimiter, as explained in Add a line break with a formula.

Join a column into one cell

TEXTJOIN works the same way with a column of values. In the worksheet below, the goal is to join the names in column B into one cell. The formula in D5 is:

=TEXTJOIN(", ",TRUE,B5:B12)

Join a column of cells with commas

TEXTJOIN joins the names from top to bottom and skips the empty cell in B8. A range with more than one row and column also works: TEXTJOIN reads it row by row. To join only the values that meet a condition, like the names in one group, see Look up and return to single cell. For more examples, including unique values in one cell, see the TEXTJOIN function page.

SUBSTITUTE and TRIM (Excel 2016 and older)

Before TEXTJOIN arrived in Excel, joining cells with a comma required a workaround based on SUBSTITUTE and TRIM. In the worksheet below, the formula in H5, copied down, is:

=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5&" "&F5)," ",", ")

Join cells with comma using SUBSTITUTE and TRIM

Working from the inside out, the formula first joins the values in the 5 cells using the concatenation operator (&) and a single space between each value:

B5&" "&C5&" "&D5&" "&E5&" "&F5

This part of the formula is annoyingly manual. To speed things up, copy &" "& to the clipboard before you start. Then follow this pattern:

[click] [paste] [click] [paste] [click] [paste]

until you get to the last cell reference. It actually goes pretty fast.

The result of this concatenation (before TRIM and SUBSTITUTE run) is a string like this:

"figs  apples  "

Next, the TRIM function is used to "normalize" all spacing. TRIM automatically strips space at the start and end of a given string, and leaves just one space between all words inside the string. This takes care of extra spaces caused by empty cells.

"figs apples"

Finally, the SUBSTITUTE function is used to replace each space (" ") with a comma and space (", "), returning text like this:

"figs, apples"

To join cells with another delimiter, adapt the new_text argument inside SUBSTITUTE. For example, to join cells with a forward slash, use " ","/" at the end of the formula.

The limitation: SUBSTITUTE replaces every space, including spaces inside a value. In row 8, "star fruit" becomes "star, fruit". If your values can contain spaces, test each cell with IF instead, add the delimiter in front of each value, and remove the first delimiter with MID:

=MID(IF(B5<>"",", "&B5,"")&IF(C5<>"",", "&C5,"")&IF(D5<>"",", "&D5,"")&IF(E5<>"",", "&E5,"")&IF(F5<>"",", "&F5,""),3,1000)

MID starts at character 3 to skip the first comma and space. This formula works in any version of Excel and leaves "star fruit" intact.

Video: How to concatenate with line breaks (3 min, free). It's one of 117 lessons in our Core Formula course.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related formulas

Excel Join first and last name

Join first and last name

To combine a first name to a last name in Excel you can use the TEXTJOIN function. In the worksheet shown, the formulas in cell F5 and H5 look like this: =TEXTJOIN(" ",1,B5,D5) // first and last =TEXTJOIN(" ",1,B5:D5) // first, middle, and last The first formula returns a full name composed of the …
Excel Look up and return to single cell

Look up and return to single cell

To look up and return all matches to a single cell, you can use a formula based on the FILTER function and the TEXTJOIN function. In the worksheet shown, the formula in cell F5, copied down, looks like this: =TEXTJOIN(", ",1,FILTER(name,team=E5)) As the formula is copied down, it returns the names …
Excel Add a line break with a formula

Add a line break with a formula

To add a line break with a formula, you can use the CHAR function. In the worksheet shown the formula in F5, copied down, is: =TEXTJOIN(CHAR(10),1,B5:D5) This formula uses the TEXTJOIN function to add line breaks between three text values. See below for another formula that uses manual concatenation …
Excel Join date and text

Join date and text

To join a date with text, you can use concatenation with the TEXT function to control the date format. In the example shown, the formula in D5 is: ="The date is "&TEXT(B5,"dddd, mmmm d, yyyy") The result is the text string "The date is Friday, December 31, 1999". As the formula is copied down the …
Excel Last updated date stamp

Last updated date stamp

To add a date stamp in a workbook to indicate a "date last updated", you can use the TEXT function. In the example shown, the formula in C5 is: ="Last update: "& TEXT(B5, "ddd, mmmm d, yyyy")

Related videos

Excel How to concatenate with line breaks

How to concatenate with line breaks

In the real world, you often need to concatenate values in a way that includes line breaks and other punctuation. In this video we'll look at a clever way to make this task easier and less error-prone. A common example of a situation that requires concatenation is assembling a mailing address from …
Excel How to use concatenation to clarify assumptions

How to use concatenation to clarify assumptions

One of the most basic and useful features in Excel is the ability to concatenate values with text. CONCATENATE is just a fancy word for "join." In this video we'll look at a simple way to use concatenation with the TEXT formula to highlight assumptions in a break-even model. This model calculates …
Excel How to change case with UPPER LOWER and PROPER

How to change case with UPPER LOWER and PROPER

When you're working with text in Excel, you'll frequently need to change case. In this video we'll look at three functions that allow you to easily change case of text in Excel: UPPER, LOWER, and PROPER. In this worksheet, we have two columns that contain names. Column B contains last names in …

Related functions

Excel TEXTJOIN Function

TEXTJOIN Function

The Excel TEXTJOIN function joins text from cells, ranges, and other values into one text string, with a delimiter between each value. TEXTJOIN can skip empty cells, and it is the standard way to combine a list of values into one cell.
Excel SUBSTITUTE Function

SUBSTITUTE Function

The Excel SUBSTITUTE function replaces text in a given string by matching. You can use SUBSTITUTE to replace or completely remove matched text. SUBSTITUTE is case-sensitive and does not support wildcards.
Excel TRIM Function

TRIM Function

The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text.