Click Data > Text to Columns, see screenshot:ģ. Select the cells that you want to split.Ģ. Click to download Kutools for Excel!Īctually, in Excel, the Text to Columns feature is opposite of the concatenate function, you can apply it to split cell values into multiple cells by any separators you need.ġ. With Kutools for Excel’s Split Cells utility, you can quickly split text string in a cell into multiple columns or rows by space, comma, new line and any other separators you specified. Reverse concatenate function to split cells into separate columns Note: In the above formula, you can also split the cell values to multiple cells based on any other separator, you just need to change the comma between the quotes to other delimiters you want.

And then go on dragging the fill handle down to the range of cells to apply this formula, and all cell values have been split into separated cells by a comma, see screenshot: Enter this formula: =TRIM(MID(SUBSTITUTE($A2,',',REPT(' ',999)),COLUMNS($A:A)*999-998,999)) into a blank cell where you want to put the result, and drag the fill handle right to the cells to contain this formula, see screenshot:Ģ. In Excel, to split cell value into multiple cells, the following formula can help you, please do as follows:ġ. Reverse the concatenate function to split cell value to multiple cells with Kutools for Excel

Reverse the concatenate function to split cell value to multiple cells with Text to Columns Reverse the concatenate function to split cell value to multiple cells with formula Have you ever thought of reversing the Concatenate function in Excel? How to reverse the concatenate function in Excel?Īs we all known, the Concatenate function in Excel can help us to combine multiple cells into one cell, but, sometimes, you want to do the opposite of Concatenate function, it means not combine cells, but split cell value into multiple separated cells.

Concatenate in openoffice excel