Reverse Concatenate Formula in Excel

This post will guide youw how to reverse the concatenate function in Excel. 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.

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.

reverse concatenate function1

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

Split text string to Multiple Cells with Text To Column Feature


You can alos 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:
#1 select the range of cells that you want to reverse.

reverse concatenate function2
#2 go to DATA tab, click Text To column command under Data Tools group. and the Convert  Text to Columns Wizard dialog will open.

reverse concatenate function3
#3 select Delimited option in the Original data type section, then click Next  button.

reverse concatenate function4
#4 check Other checkbox, and type the delimiter that you want to use, such as: a comma character. and then click Next button.

reverse concatenate function5
#5 select General option under column data format section, and select a destination cell where you want to place the split results.  click Finish button.

reverse concatenate function6 #6 Let’s see the result:

reverse concatenate function7

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)….
Related Posts
How to Remove Middle Name from Full Name in Excel
Remove Middle Name from Full Name 6

When we get a list of full names, we can remove the middle name for short in some cases. As the middle names are different among the full name list so we cannot replace them by space directly in excel. ...

How to Count Comma Separated Value in One Cell in Excel
count value separated by comma2

This post will guide you how to count values in a single cell separated by commas with a formula in Excel. How do I count comma separated values in one cell in Excel 2013/2016. Is it possible to have a ...

How to Remove the First/Last Word from Text string in Cell
remove first last word in cell2

This post will guide you how to remove the first and the last word from a text string in cells in Excel. How do I use a formula to remove first and last word of a text string in Excel. ...

Extract Email Address from Text
exctract email address from text3

This post will guide you how to extract email address from a text string in Excel. How do I use a formula to extract email address in Excel. How to extract email address from text string with VBA Macro in ...

Remove Trailing Spaces from Cells in Excel
remove trailing spaces5

This post will guide you how to remove leading and trailing spaces in cells in Excel. How do I remove leading or trailing spaces in a range of cells with a formula in Excel. How to remove trailing spaces from ...

Convert Scientific Notation to Text or a Number in Excel
convert scientific notation to text7

This post will guide you how to convert scientific notation to text or a number in Excel. How do I convert excel scientific to text with a formula in Excel. Convert Scientific Notation to Text or a Number in Excel ...

Remove Spaces from a String
remove spaces2

This post will guide you how to remove extra spaces between words or text string from the specified range of cells in excel. How to delete all spaces from excel cells. For example, when you paste data from an external ...

Remove Leading and Trailing Spaces
remove leading and trailing spaces112

In this tutorial you will learn that how to remove leading or trailing spaces from a text string in a cell in Excel. This post will guide you how to delete leading or trailing spaces of all cells in a ...

How to Count Spaces before the Text String
count spaces before text2

This post will guide you how to count the number of spaces before the text string in a cell in Excel. How to count the leading spaces before the text string in one cell with formula in excel. How do ...

How to extract nth word from text string
extract nth word4

This post will guide you how to extract the nth word from a text string in excel. How do I get the nth word from text string in a cell using excel formula. How to write a User Defined Function ...

Sidebar