If we lived in a perfect world, all data would always be in the right structure and format for the current job. But we often have to clean up imported data or restructure our own data before sending it to others.
That’s what happens when someone asks you to combine all the values in a column into a single cell. That’s not what you need to do Microsoft Excel, but the next person might need it that way. In this guide, I will show you several ways to fulfill this requirement.
UNDERSTAND: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)
I’m using Microsoft 365 on Windows 10 64-bit systems, but you can use older versions for the first method. Each section deals with versions that support that method. Excel for the web supports all three methods. For your convenience, you can download demo .xlsx and .xls files.
How to combine values into a single cell in an Excel .xls file
Newer features and functions that I will use later in this article were not available in the .xls format earlier. We will address this requirement using a helper column and a simple expression.
Picture A shows a column of names that we’ll combine into one cell with a separator between the values. We’ll use the helper column to add a delimiter to each value. To do this, enter the following expression in C3 and copy it into the remaining cells:
=B3&","
Picture A
Character & combines values - it doesn’t sum or do any math evaluation. The element “,” is the delimiter, comma (,).
The delimiter is a character that separates values or represents a data type. You can use almost any character as the delimiter in this case, but the recipient will likely 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 the running total, but instead of summing, we’ll concatenate.
In D3, reference the first helper value by typing C3. In D4, enter the following expression and copy it into the remaining cells:
=D3&C4
As you can see in Figure BUTeach expression appends a value to the list.
Figure BUT
You may have noticed that the list ends with a comma character. To remove that comma, reference only the name in the last expression. Doing so will remove the comma at the end of the list.
You may be wondering about Excel’s CONCATENATE() function, but you must reference each cell individually and include a comma character between each reference. It’s too much of a hassle, 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 will use the same running sum expression.
This function is available in Excel 2010 through Microsoft 365 and uses the following syntax:
CONCATENATE(text1, [text2], ...)
where each argument refers to a text value or a string of characters, such as the comma character. SIZE display the result of using this function. First, enter the simple expression
=B3&","
in C3. Then enter the following function in C4 and copy it into the rest of the cells:
=CONCATENATE(C3,B4,",")
SIZE
It’s the same expression we used earlier, but it omits the need for a helper column. Also, you’ll probably need to remove the last comma in the string.
At this point, you can submit the list or use it however you need. If you are using Excel 2019 through Microsoft 365, there is a function that can do the same thing, just faster and without the help column.
How to combine values into a single cell using TEXTJOIN() in Excel
Microsoft 365, Excel 2019, and Excel for the web support the TEXTJOIN() function. This function concatenates values from multiple ranges and allows you to set delimiters. 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 refer to the values you want to concatenate. To illustrate, enter the following function into C3 – no need to copy it into the rest of the cells:
=TEXTJOIN(",",TRUE,B3:B9)
As you can see in Visualization, we achieved the same result with just one function. Also, there is no comma at the end of the string. This function has one restriction: The concatenated string cannot exceed 32,767 characters.
Visualization
Keep stable
You can also use Power Query to complete this task, but for this example it would be overkill. However, in a next article, I will show you a PivotTable solution that joins values in groups.
