TextToColumns

​

Overview

When working with certain tables, you might encounter text columns that contain multiple values separated by specific characters such as commas or semicolons. Use the TextToColumns gem to parse this text and simplify further analysis and processing.

​

Prerequisites

• Add prophecy_basics package version 1.0.0 or higher to your project.

​

Parameters

​

Example

Let’s say you have the following table that includes bank account information. Note that some values in the Beneficiaries column contain multiple names separated by semicolons. You can use the TextToColumns gem to automatically split these values into separate rows or columns, making your data cleaner and easier to work with.

1. Open the TextToColumns gem.
2. Select the Beneficiaries column to split.
3. Under Delimiter, input ; as the delimiting character.
4. Select the split strategy.

Let’s explore the outputs that result from the different split strategies.

​

Split to columns

When you use the Split to columns strategy, each name should be split into its own column.

1. Select Split to columns.
2. Under Number of columns, type 2.
3. Keep the default Extra Characters setting to Leave extra in last column.
4. Keep or change the default column prefix and suffix.

The resulting table will have two new columns. The output table has the same number of rows as the input table. Notice that one the of cells still has a semicolon: Leila Haddad; Ivan Petrov. This is because the gem was configured to generate two new columns. Extra characters are kept in the last column. If the gem was configured to generate three columns instead, each beneficiary would have their own column.

​

Split to rows

The Split to rows strategy creates a separate row for each value in the selected column. All other column values are copied to each new row. Use this strategy when:

• The number of items in a cell (such as beneficiaries) varies between rows.
• You don’t know the maximum number of items in the column.

To apply this strategy:

1. Select Split to rows.
2. Keep or change the default generated column name.

In the output table, each beneficiary has their own row. The output table has more rows than the input table.