Opposite of Concatenate in Excel (4 Options)

In Microsoft Excel, there is no exact function that works as the opposite of concatenate function. But still, we have a number of suitable procedures and alternatives to split a text into multiple parts. In this article, you’ll learn how you can use those simple techniques that should function as the opposite of concatenate in Excel.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

4 Methods That Work As Opposite of Concatenate in Excel

1. Split Text into Multiple Cells with Formula

In the following table, Column B contains five distinct cells, each of which has three random names separated by a common delimiter ‘Comma’ (,). Under three headers named Part 1, Part 2 and Part 3 will occupy the names separately from each cell in Column B.

📌 Step 1:

➤ Select Cell C5 and type:

📌 Step 2:

➤ Press Enter and you’ll get the first name 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 comma with the repeated spaces mentioned in the previous step. Thus 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.
• Finally, the TRIM function removes all unnecessary spaces from the text string found by the MID function and returns the name ‘Patrick’ exactly.

Now let’s follow the rest of the steps to extract all other split names.

📌 Step 3:

➤ From Cell C5, use Fill Handle to drag the cell rightward until you get three split names.

So, we’ve just extracted three names separately from Cell B5.

📌 Step 4:

➤ Now drag down the Fill Handle to autofill the rest of the cells ranging from C6 to E9.

And you’ll find all the names split from the groups of names present in Column B.

Read More: Combine Multiple Cells Into One Cell Separated By Comma In Excel

Similar Readings

• How to Concatenate Multiple Cells With Space in Excel (7 Methods)
• Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)
• How to Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)
• Merge Rows in Excel (5 Easy Ways)
• How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)

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

Now we have some random full names in Column B. We’ll split the names and show them separately in the next two cells in a similar row.

📌 Step 1:

➤ Select the first output Cell C5 and type:

📌 Step 2:

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

You’ll get all the first names separated from the full names present in Column B.

🔎 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 a number of characters specified by the FIND function previously.

Now, let’s see how we can pull out the second part of each name.

📌 Step 3:

➤ In Cell D5, the required formula will be:

📌 Step 4:

➤ After pressing Enter and auto-filling the entire Column D, you’ll find all the last names only under the Part 2 header.

🔎 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.
• Finally, the RIGHT function extracts 15-8=7 characters from the right and returns the name ‘Cummins’.

Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)

3. Use Text to Column Wizard to Reverse Concatenate in Excel

Let’s go to our first example again. Now we’ll use Text to Column wizard to split cells after removing delimiters.

📌 Step 1:

➤ Select the range of cells (B5:B9) containing all text data that have to be split.

➤ Under the Data tab, select the Text to Columns option from the Data Tools group of commands.

A dialogue box will open up.

📌 Step 2:

➤ Choose the radio button ‘Delimited’ as the original data type.

➤ Press Next.

📌 Step 3:

➤ From the Delimiters options, mark on Comma only and leave other options unmarked. You’ll be shown a data preview like in the following picture.

➤ Press Next.

📌 Step 4:

➤ Keep the option ‘General’ as Column Data Format.

➤ Enable editing in the Destination box and select the output cells ranging from C5 to E9.

➤ Press Finish and you’re done.

You’ll be displayed all the names split in the selected output range of cells right away.

Read More: Combine Multiple Columns into One Column in Excel

Similar Readings

• Excel INDEX MATCH to Concatenate Multiple Results (With 2 Easy Steps)
• How to Concatenate with Space in Excel (3 Suitable Ways)
• Concatenate Email Addresses in Excel (4 Effective Ways)
• How to Bold Text in Concatenate Formula in Excel (2 Methods)
• Carriage Return in Excel Formula to Concatenate (6 Examples)

4. Apply Flash Fill Method to Work as Opposite of Concatenate

In our last example, we’ll try to apply the Flash Fill to extract names separately from the cells. The Flash Fill follows a pattern to fill data automatically in Excel. But if any pattern is not found, the Flash Fill won’t work out effectively and even might result in erroneous output. This method is great and useful only if you have a regular and symmetric pattern in your data range.

Now let’s see how this Flash Fill command works swiftly to separate texts.

📌 Step 1:

➤ Select Cell C5 and type ‘Patrick’ manually.

📌 Step 2:

➤ Use Fill Handle to drag down to the last Cell C9.

➤ Click on the options and select Flash Fill now.

And you’ll find all the first names separated and extracted in Column C.

📌 Step 3:

➤ Similarly, do it now for the last name under the Part 2 header.

And you’ll see all the last names at once separated from the full names present in Column B.

Read More: Concatenate Not Working in Excel (3 Reasons with Solutions)

Concluding Words

I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Further Readings

• Combine Text in Excel (8 Suitable Ways)
• How to Combine Date and Text in Excel (5 Ways)
• Concatenate Numbers in Excel (4 Quick Formulas)
• How to Concatenate Two Columns In Excel (5 Methods)
• Combine Text from Two or More Cells into One Cell in Excel (5 Methods)
• How to Add a 1 in Front of Numbers in Excel (7 Easy Ways)
• Concatenate Date and Time in Excel (4 Formulas)