Excel Range Operations

Spreadspeed offers spreadsheet utilities that operate on the workbook level, the worksheet level, and the range level. This page highlights the utilities that operate on the range level.

Split a Single Column into Multiple Columns (Column to Array)

The Split Single Column into Multiple Columns tool will take a single input column and split it into an array of rows and columns. The tool allows you to specify a wide set of split criteria, and start a new every time the criteria is matched. The Split Columns tool is perfect for automatically re-organizing series data imported from other sources. The possible split criteria area:

• Split on a fixed number of cells
• Split On or After a matching criteria is found
• Split by data type (number, text, date, blank cell)
• Split by number value (<, <=, =, >>, >)
• Split by text match, using entire cell content, wildcards, or regular expressions.

Split Column Across Multiple Columns Screenshot

Split a Single Row into Multiple Rows (Row to Array)

This tool works the same way as the Split Single Column into Multiple Columns tool, except that it works on a single row of data and will start a new row when the split criteria is matched.

Concatenate (Merge) Columns or Rows

Given a selected range, the Concatenate tool will create a new column (or row) joining the contents in each cell, separated by an optional delimeter. If specified, having ‘\n’ in the delimiter field will place a line break within the resulting cell output.

Concatenate Screenshot

Concatenate Dialog

Sample Input and Output

Transpose in Place

The typical method of transposing a range of cells is to copy the range, use Paste Special with the Transpose option, and then finally delete the original cells. With Transpose in Place, the range is transposed in a single step, overwriting the original content.

Transpose in Place Example

Swap Ranges

The Swap ranges tool will two ranges of the same size and swap their contents, number formats, and optionally their cell styles. The tool also works great for swapping columns or rows.

Swap Ranges Screenshot