If you have ever needed to split text that was combined using the concatenate function, and you may be want to know if there is a way to reverse the process and spearate the text into its orginal form.

This post will guide you how to **reverse the concatenate function** using a **formula **in Excel, as well as “**Text to Columns**” feature. How do I split the text string into the muliptle cells with a formula in Excel. How to split text data of a cell to multiple cells in Excel.

Table of Contents

## 1. Video: How to Reverse Concatenate Formula in Excel

This video tutorial demonstrates the steps for using a **formula **and the “**Text to Columns**” feature to split text in Excel.

## 2. Reverse Concatenate Formula

To reverse concatenate formula in Excel, you can use a formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, and the COLUMNS function.

Assuming that you have a list of data in range B1:B4 that contain text string, and you want to split the string by comma character. you can write down the following formula:

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

Type this formula into a blank cell and then press Enter key and then drag the Fill Handle down to other cells to apply this formula.

You will see that all text string in range of cells **B1:B4** have been split into multiple cells by a comma character.

This method can be useful for working with data that has been combined using the concatenate function or for importing data from other sources that may be in a combined format.

## 3. Split text string to Multiple Cells with Text To Column Feature

The opposite of concatenate in excel is to split or separate text that have been combined using the concatenate function. If you want to split text into separate cells in excel, you can also use the **Text To column** Feature to reverse the concatenate formula to split text string in the range of cells **B1:B4** to multiple cells in Excel. Just do the following steps:**Step1:** select the range of cells that you want to reverse.

**Step2:** go to **DATA** tab, click **Text To column** command under** Data Tools** group. and the **Convert Text to Columns Wizard** dialog will open.

**Step3:** select **Delimited** option in the **Original data type** section, then click **Next ** button.

**Step4**: check **Other** checkbox, and type the delimiter that you want to use, such as: a comma character. and then click **Next** button.

**Step5:** select **General** option under **column data format** section, and select a destination cell where you want to place the split results.

** **

**Step6:** Once you are satisfied with the preview, click on “**Finish**” to split the text into separate columns.

Your text will now be split into separate columns based on the delimiter or width you specified.

## 4. Related Functions

- Excel Substitute function

The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE (text, old_text, new_text,[instance_num])…. - Excel TRIM function

The Excel TRIM function removes all spaces from text string except for single spaces between words. You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)…. - Excel MID function

The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…. - Excel REPT function

The Excel REPT function repeats a text string a specified number of times.The syntax of the REPT function is as below:= REPT (text, number_times)… - Excel COLUMNS function

The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….

## Leave a Reply

You must be logged in to post a comment.