Opposite of Concatenate in Excel (4 Options)

Method 1 – Split Text into Multiple Cells with Formula

In the following table, Column B contains five distinct cells, each of which contains three names separated by a common delimiter ‘Comma’ (,). The names will be entered into three separate columns with headers Part 1, Part 2, and Part 3.

Step 1:

Select Cell C5 and enter the below formula.

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

Step 2:

Press Enter and the first name will be split from the names in Cell B5.

How Does the Formula Work?

REPT(” “,999): Here the REPT function repeats the character ‘space’ 999 times inside the SUBSTITUTE function.
SUBSTITUTE($B5,”,”,REPT(” “,999)): The SUBSTITUTE function substitutes commas with the repeated spaces in the previous step. The formula returns the name Patrick with spaces.
COLUMNS($A:A)*999-998: The COLUMNS function here counts the number of columns and assigns the resultant value as the start_num for the MID function.
MID(SUBSTITUTE($B5,”,”,REPT(” “,999)),COLUMNS($A:A)*999-998,999): The MID function returns the names ‘Patrick’ with 999 characters in total.
The TRIM function removes all unnecessary spaces from the text string found by the MID function and returns only the name ‘Patrick’.

Step 3:

From Cell C5, use the Fill Handle to drag the formula to the right until all three names have been separated.

Step 4:

Drag the Fill Handle down to autofill the rest of the cells ranging from C6 to E9.

All the names from the groups of names in Column B have been separated.

Method 2 – Opposite of Concatenate: Use of Text Functions to Split into Multiple Cells

Step 1:

Select the first output Cell C5 and enter the below formula.

=LEFT(B5,FIND(" ",B5)-1)

Step 2:

Press Enter and use Fill Handle to autofill the rest of the cells in Column C.

How Does the Formula Work?

In this formula, the FIND function looks for the first single space in Cell B5 and returns the position of the space character.
The LEFT function extracts the name from the left and returns several characters specified by the FIND function previously.

Step 3:

In Cell D5, enter the following formula.

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

The surnames are returned in the second column.

How Does the Formula Work?

In this formula, the LEN function returns the total number of characters available in Cell B5 and that is 15.
The FIND function returns the position of the space found in that text and returns 8.
The arithmetic difference between the two previous numerical values assigns the number of characters for the RIGHT function.
The RIGHT function extracts 15-8=7 characters from the right and returns the name ‘Cummins’.