Moving example data to a new field

Question

So I want to move the below data to a new field when its separated by a "." or "-". See below:

10020 · BANK - BMO10025 · BANK - BMO got12000 · PETTY CASH:12010 · Petty Cash - Andrea12000 · PETTY CASH:12012 · Petty Cash - Liz

So what it should look like is:

Column 1       Column 2          column 3

10020              Bank                BMO

10025              Bank                BMO got

Thableaus · Accepted Answer

Hi @JamesG2806

Here's a dynamic solution for your parsing problem:

- Add a Record ID

- Use Tokenize Method with Regex Tool with the Formula:

(.*?)(?:·|-|$)

Split to rows

- Use Tile Tool to create the sequence of columns

- Use Cross-Tab to divide into columns and group by Record ID.

- Data Cleansing tool to remove leading and trailing whitespaces

WF appended.

Cheers,