Expand

The Expand function is designed to separate and distribute data from a single column into multiple rows or columns based on a delimiter you specify.

See it in Action

Requirements

Data

• Target Data

  • Data Range: Identify the cells range where you want to apply the function.

  • Columns included: Check this option if your selected range includes the first row as column headers. This ensures that the first row is recognized as column names and excluded from the processing.

• Column to Expand: Specify the column containing the data you wish to separate into multiple rows or columns.

• Direction

  • Vertical: Select this option to distribute the separated data down multiple rows in a single column.

  • Horizontal: Choose this to spread the separated data across multiple columns in a single row.

• Delimiter: Enter the character or string used to separate the data within your selected column. The Expand function will use this delimiter to determine where to split the data.

Fill empty cells with

• Keep Empty: Leave cells that are empty as is, without adding any text.

• Forward Fill: Copy the value from the nearest non-empty cell above into empty cells before adding the specified letters or text.

• Backward Fill: Copy the value from the nearest non-empty cell below into empty cells before adding the specified letters or text.

• -: Fill empty cells with a letter "-".

• 0: Fill empty cells with a letter "0".

• Custom: Allows for a custom value or text to be inserted into empty cells prior to adding the specified letters.

Result Destination

Specify the starting cell where the results will be placed.