Separate_To_Rows

Function separates selected variable (column) values into multiple rows.

Properties

Keywords

Separate Excel Column to Rows, One Excel Column into Multiple Rows, Split Excel Column to Rows, Split Cell to Rows, Divide Excel Column to Rows, Split Text to Rows, Excel Text to Rows

See Also

Separate, Gather, Transpose

Video-tutorial

How to Separate Excel Column into Rows

Often, in Excel, we need to separate values stored in one column into multiple columns. Less often it happens that after dividing the values of one column, we need to store them one another in the same column. For this purpose, Excel does not contain a simple to use feature. The Reshape.XL add-in includes a simple and quick-to-use function - Separate_To_Rows. An example of its use is shown in the following figure.

For the purpose of this tutorial, we will use a very simple table consisting of two variables - Group and Vals. Our task is to divide the values from the column Vals to individual values and store them in the same column.

In the Variables ribbon toolbar tab, click on the Separate_To_Rows button and the function will appear in the Side Panel.

For the application, we define four parameters in the Properties Panel. In the Variable combo-box select the column from the input table for values splitting. Then, in the Separator property, define a separator of the individual values - a comma in our case. If you press the Run button, the program separates the values and inserts them under each other. Values from other variables are replicated in new rows.

In addition to the properties described, you can define two more properties. The Into property determines the name of the new variable. In the following example, we used the “Col_Name” value as the new name. Use the Remove Input check-box to determine whether the input variable will be left in the resulting table. This column is deleted by default. If the property is set to FALSE, the variable is left.

A Separator property can be defined in two ways - as a static text value or in the form of regular expressions. Regular expression is a bit more complex. On the other hand, the application of this procedure allows you to create very advanced types of cell value separation. A more detailed description of this approach is described in the Separate function. Separate_To_Rows use regular expressions in the same way.

Separate_To_Rows is a quick and easy to use function. Despite its ease, it has no direct alternative in Excel. If you want to separate values into columns, use the basic Separate function.