Opposite of Concatenate in Excel (4 Options)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, there is no exact function that works as the opposite of concatenate function. But still, we have several 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 concatenating in Excel.


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’ (,). 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 commas 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 the 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

Read More: How to Concatenate with Delimiter in Excel


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

Read More: How to Concatenate with Space in Excel


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

Let’s go to our first example again. Now we’ll use the 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

Read More: How to Concatenate Apostrophe in Excel


4. Apply the Flash Fill Method to Work as Opposite of the 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 the 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

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

Read More: How to Bold Text in Concatenate Formula in Excel


Download Practice Workbook

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

3 Comments
  1. I am using a form on my website to collect CVs and the excel output is like below:

    Degree | College Name | Discipline | Year of Graduation | GPA
    Masters | Oxford | Mathematics | 2020 | 88
    Bachelors | Cambridge | Chemistry | 2016 | 76
    Diploma | George’s School | Arts | 2012 | 94

    All the above lies in a single cell in excel / CSV file.

    Can anybody please help me how I can rearrange all this into an excel table with each cell showing part of the entries.
    I do not want to use the “Text to Columns” method, because there are several such tables created and they have to be done automatically by a formula.

    I appreciate your solutions.

    best regards,
    Nasser

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo