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.

Opposite of Concatenate: Split into Multiple Cells with Formula

📌 Step 1:

➤ Select Cell C5 and type:

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

Opposite of Concatenate: Split into Multiple Cells with Formula

📌 Step 2:

➤ Press Enter and you’ll get the first name split from the names in Cell B5.

Opposite of Concatenate: Split into Multiple Cells with Formula

🔎 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.

Opposite of Concatenate: Split into Multiple Cells with Formula

📌 Step 4:

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

Opposite of Concatenate: Split into Multiple Cells with Formula

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

Opposite of Concatenate: Split into Multiple Cells with Formula


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.

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

📌 Step 1:

➤ Select the first output Cell C5 and type:

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

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

📌 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.

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

🔎 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:

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

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

📌 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.

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

🔎 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’.

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.

Opposite of Concatenate: Use of Text to Column Wizard in Excel

📌 Step 2:

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

➤ Press Next.

Opposite of Concatenate: Use of Text to Column Wizard in Excel

📌 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.

Opposite of Concatenate: Use of Text to Column Wizard in Excel

📌 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.

Opposite of Concatenate: Use of Text to Column Wizard in Excel

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

Opposite of Concatenate: Use of Text to Column Wizard in Excel


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.

Opposite of Concatenate: Use of Flash Fill Method

📌 Step 2:

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

➤ Click on the options and select Flash Fill now.

Opposite of Concatenate: Use of Flash Fill Method

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

Opposite of Concatenate: Use of Flash Fill Method

📌 Step 3:

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

Opposite of Concatenate: Use of Flash Fill Method

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

Opposite of Concatenate: Use of Flash Fill Method


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.


You May Also Like to Explore

Lookup and Return Multiple Values Concatenated into One Cell in Excel

How to Concatenate Multiple Cells in Excel

How to Concatenate in Excel (3 Suitable Ways)

How to Use CONCATENATE Function in Excel (4 Examples)

How to Concatenate Two Columns In Excel ( 5 Simple Methods)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo