How to concatenate values in a single Excel column to a single row

If we lived in a perfect world, all data would always be in the right structure and format for the job at hand. But we often have to clean up imported data or restructure our own data before sending it off to someone else.

That’s what happens when someone requests that you combine all the values in a column into a single cell. It’s not something you’d need to do in Microsoft Excel, but the next person down the line might need it that way. In this tutorial, I’ll show you a few ways to fulfill this requirement.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use older versions for the first method. Each section mentions the versions that support that method. Excel for the web supports all three methods. For your convenience, you can download the demonstration .xlsx and .xls files.

How to combine values into a single cell in .xls Excel files

The newer features and functions that I’ll be using later in this article aren’t available in the earlier .xls format. We’ll tackle this requirement by using a helper column and a simple expression.

Figure A shows a column of first names that we’ll combine into one cell with a delimiter between the values. We’ll use a helper column to add a delimiter to each value. To accomplish this, enter the following expression in C3 and copy to the remaining cells:

=B3&","

Figure A

The & character combines values — it doesn’t sum or perform any mathematical evaluation. The “,” component is the delimiter, a comma (,).

A delimiter is a character that separates values or denotes the data type. You can use most any character as the delimiter in this situation, but the recipient will probably specify the character. As you can see in column C, a comma follows each name.

With the helper column in place, we’ll use a simple expression to combine them all. We’ll treat the values in the helper column as values in a running total, but instead of summing, we’ll concatenate.

In D3, reference the first helper value by entering C3. In D4, enter the following expression and copy it to the remaining cells:

=D3&C4

As you can see in Figure B, each expression concatenates one more value to the list.

Figure B

You might have noticed that the list ends with a comma character. To eliminate that comma, reference only the name in the last expression. Doing so will eliminate the comma from the end of the list.

You might be wondering about Excel’s CONCATENATE() function, but you must reference each cell individually and include the comma character between each reference. It’s too much trouble, especially if your list of values is long. However, you will eliminate the need for a helper column. If you decide to use it, you’ll use the same running total expression.

This function is available in Excel 2010 through Microsoft 365 and uses the following syntax:

CONCATENATE(text1, [text2], ...)

where each argument references a text value or a literal string, such as a comma character. Figure C shows the results of using this function. First, enter the simple expression

=B3&","

in C3. Then, enter the following function into C4 and copy to the remaining cells:

=CONCATENATE(C3,B4,",")

Figure C

It’s the same expression we used earlier, but it omits the need for a helper column. In addition, you’ll probably need to delete the last comma in the string.

At this point, you can send the list or use it however you need to. If you’re using Excel 2019 through Microsoft 365, there’s a function that can do the same thing, only quicker and with no helper column.

How to combine values into a single cell using TEXTJOIN() in Excel

Microsoft 365, Excel 2019 and Excel for the web support the function, TEXTJOIN(). This function concatenates values from multiple ranges and allows you to set a delimiter. This function uses the following syntax:

TEXTJOIN(delimiter, ignore_empty, range1, [range2], …)

These arguments are self-explanatory for the most part. If ignore_empty is TRUE, TEXTJOIN() will ignore empty cells. The range arguments reference the values you want to concatenate. To demonstrate, enter the following function into C3 — there’s no need to copy it to the remaining cells:

=TEXTJOIN(",",TRUE,B3:B9)

As you can see in Figure D, we accomplished the same result with only one function. In addition, there’s no comma at the end of the string. This function does have one limitation: The concatenated string can’t exceed 32,767 characters.

Figure D

Stay tuned

You can also use Power Query to complete this task, but for this example it would be overkill. However, in a future article, I’ll show you a PivotTable solution that concatenates values by groups.