13 Ways How to Separate Excel Columns

Published on 28 Jul, 2019 by Milos Gregor

One of the most common features we use in Excel is to separate values from one column to multiple. Likewise, this function is often used in other environments (such as programming languages) to prepare input data for further analysis. This article shows you the capabilities that the Reshape.XL interface provides for these purposes.

For separation purposes, we use the Separate function in the Reshape.XL add-in, located in the ribbon toolbar tab Variables. Using different settings we can get very different results. With the built-in Excel features, you can obtain similar results only if you use advanced combination of several complex functions.

You can get up to 13 different results from a single column. The columns can be separated by defining the Separation Mode. The function contains three separation modes - simple string separation, positional separation or complex separation based on regular expressions (regex separation).

Furthermore, you can define the alignment of the separated values (if there is not a constant number of separated values) or you can define the maximum number of separated values. Subsequently, it is also possible to define what happens with the rest of the separated values (properties extra and fill).

For a complete picture of the Separate function capabilities and ease of setup, see the following video tutorial, which illustrates the function in practical use.

As you can see from the video, the Separate function offers very complex options for separating values in Excel spreadsheet columns. On the other hand, it is very easy to set up and use. For a comprehensive description, see the documentation in the How To section.

The Reshape.XL add-in has one more (and interesting) separation function - Separate_To_Rows. This function also separates the values from the selected column. The difference is that the resulting separated new values are written below, in the same column. The content of other columns is simply copied.