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.

**Table of Contents**hide

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

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

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

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

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

**📌**** 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:

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

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

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

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

**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)
- Concatenate Numbers in Excel (4 Quick Formulas)
- Combine Multiple Cells Into One Cell Separated By Comma In Excel
- Combine Text from Two or More Cells into One Cell in Excel (5 Methods)
- How to Combine Date and Text in Excel (5 Ways)
- Concatenate Date and Time in Excel (4 Formulas)
- Combine Multiple Columns into One Column in Excel
- How to Concatenate Two Columns In Excel (5 Methods)

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

Hello Nasser Enami, you can follow this article to solve your problem.

https://www.exceldemy.com/separate-address-in-excel-with-comma

In this article, you will find how to separate an address into a city, state, and zip code using an Excel formula. You can modify this file for your purpose. In your dataset the separator is “|” and you have to use 3 separators. Modify this worksheet as shown in the screenshot below-

Let us know the outcome in the reply. Thank you!

We shall try to help. Thanks.