> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# TextToColumns

> Convert text into a column in your table

export const gemName_0 = "TextToColumns"

export const execution_engine_0 = "the SQL warehouse"

<Info>This gem runs in {execution_engine_0}.</Info>

## 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.

<Tip>
  The {gemName_0} gem has a corresponding interactive gem example. See [Interactive gem
  examples](/data-analysis/gems/gems#interactive-gem-examples) to learn how to run sample pipelines
  for this and other gems.
</Tip>

## Prerequisites

* Add `prophecy_basics` package version 1.0.0 or higher to your project.

## Parameters

| Parameter              | Description                                                                                                                                                                           |
| ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Select Column to Split | The column that contains the text you would like to split.                                                                                                                            |
| Delimiter              | The character that delimits the separate values.                                                                                                                                      |
| Select Split Strategy  | <ul><li>Split to columns: Values will be split into individual rows in one or more columns.</li><li>Split to rows: Values will be split into individual rows in one column.</li></ul> |

## 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.

| `Account_Number` | `Account_Type` | `Balance` | `Beneficiaries`                          |
| ---------------- | -------------- | --------- | ---------------------------------------- |
| 123456789        | Checking       | 75000.50  | `Amina Yusuf; Juan Pérez`                |
| 987654321        | Savings        | 12000.00  | `Chen Wei`                               |
| 456789123        | Checking       | 3400.50   | `Yuki Tanaka; Leila Haddad; Ivan Petrov` |
| 789123456        | Checking       | 800.00    | `NULL`                                   |
| 321654987        | Savings        | 2200.95   | `Mei Lin; Noah Schmidt`                  |

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.

| `Account_Number` | `Account_Type` | `Balance` | `Beneficiaries`                          | `root_1_generated` | `root_2_generated`          |
| ---------------- | -------------- | --------- | ---------------------------------------- | ------------------ | --------------------------- |
| 123456789        | Checking       | 75000.50  | `Amina Yusuf; Juan Pérez`                | `Amina Yusuf`      | `Juan Pérez`                |
| 987654321        | Savings        | 12000.00  | `Chen Wei`                               | `Chen Wei`         | `NULL`                      |
| 456789123        | Checking       | 3400.50   | `Yuki Tanaka; Leila Haddad; Ivan Petrov` | `Yuki Tanaka`      | `Leila Haddad; Ivan Petrov` |
| 789123456        | Checking       | 800.00    | `NULL`                                   | `NULL`             | `NULL`                      |
| 321654987        | Savings        | 2200.95   | `Mei Lin; Noah Schmidt`                  | `Mei Lin`          | `Noah Schmidt`              |

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.

| `Account_Number` | `Account_Type` | `Balance` | `Beneficiaries`                          | `generated_column` |
| ---------------- | -------------- | --------- | ---------------------------------------- | ------------------ |
| 123456789        | Checking       | 75000.5   | `Amina Yusuf; Juan Pérez`                | `Amina Yusuf`      |
| 123456789        | Checking       | 75000.5   | `Amina Yusuf; Juan Pérez`                | `Juan Pérez`       |
| 987654321        | Savings        | 12000     | `Chen Wei`                               | `Chen Wei`         |
| 456789123        | Checking       | 3400.5    | `Yuki Tanaka; Leila Haddad; Ivan Petrov` | `Yuki Tanaka`      |
| 456789123        | Checking       | 3400.5    | `Yuki Tanaka; Leila Haddad; Ivan Petrov` | `Leila Haddad`     |
| 456789123        | Checking       | 3400.5    | `Yuki Tanaka; Leila Haddad; Ivan Petrov` | `Ivan Petrov`      |
| 789123456        | Checking       | 800       | `NULL`                                   | `NULL`             |
| 321654987        | Savings        | 2200.95   | `Mei Lin; Noah Schmidt`                  | `Mei Lin`          |
| 321654987        | Savings        | 2200.95   | `Mei Lin; Noah Schmidt`                  | `Noah Schmidt`     |