Moving example data to a new field

[JamesG2806]

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

10020 · BANK - BMO

10025 · BANK - BMO got

12000 · PETTY CASH:12010 · Petty Cash - Andrea

12000 · 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 solution]

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,

[JamesG2806]

Perfect did the job thanks for that help!