Excel: How to Use the Opposite of Concatenate

You can use the CONCATENATE function in Excel to join text from multiple cells into a single cell.

The opposite of this function is the TEXTSPLIT function, which can be used to split the text in one cell into multiple cells based on a specific delimiter.

For example, you can use the following formula to split the text in cell C2 into multiple cells based on where spaces occur in the cell:

=TEXTSPLIT(C2, " ")

The following example shows how to use this formula in practice.

Example: How to Use the Opposite of Concatenate in Excel

Suppose we have the following list of cities and team names for various basketball teams:

We could type the following formula into cell C2 to concatenate together the city and team name into one cell:

=CONCATENATE(A2," ",B2)

We could then click and drag this formula down to each remaining cell in column C:

Now if we would like to perform the opposite of concatenate, then we could type the following formula into cell D2 to split the text of each cell in column C into multiple cells based on spaces:

=TEXTSPLIT(C2, " ")

We could then click and drag this formula down to each remaining cell in column D:

Excel opposite of concatenate

The TEXTSPLIT function splits the text of each cell in column C into multiple cells based on where the space occurs.

The end result is that we’re able to split the city and team name of each cell in column C into multiple cells.

Note #1: In this example there was only one space in each cell in column C, but the TEXTSPLIT function can work with any number of spaces in a given cell.

Note #2: You can find the complete documentation for the TEXTSPLIT function in Excel here.