You can use the Text to Columns feature on the Data tab in Excel to split the text in one column into multiple columns using a specific delimiter.
The opposite of the Text to Columns feature is the TEXTJOIN function, which allows you to combine text from multiple columns into one column using a specific delimiter.
The following example shows how to use the TEXTJOIN function in practice.
Example: How to Perform Reverse of Text to Columns in Excel
Suppose we have the following two columns in Excel that contain information about various basketball teams:
Suppose we would like to combine the text from each of these columns into one column, using a space as a delimiter.
We can type the following formula into cell C2 to do so:
=TEXTJOIN(" ", TRUE, A2:B2)
We can then click and drag this formula down to each remaining cell in column C:
Column C combines the location from column A and the team name from column B into one cell.
Note that we could also choose to use a different delimiter if we’d like in the TEXTJOIN function.
For example, we could type the following formula into cell C2 instead to combine the location and team name using a comma as a delimiter:
=TEXTJOIN(", ", TRUE, A2:B2)
The following screenshot shows how to use this formula in practice:
Column C now combines each location and team name into one cell, using a comma as a delimiter.
Note: You can find the complete documentation for the TEXTJOIN function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Convert Column into Comma Separated List
Excel: How to Use a Concatenate If Formula
Excel: How to Concatenate Values and Keep Leading Zeros
