Remove irrelevant characters & data from a column (regex maybe?)

Question

Hi Guys,

I have a need to prepare some data.

I need to map data from key branches to a target branch ID (the target needs to have this data registered against them).

The problem is that the column containing the source branch(es) is surrounded in useless text that I need to do away with.

An example of this is attached, however for this I'll show below:

target | source branch

2673     ---- :branch_code:
            :target_branch_code:
            - '12'
            - '402'
            :target_retailer_id: 1528

What I need is a column that gives me the target_branch_code numbers. Everything else can be removed.

How would I go about doing this, so that the output would be:

target | source branch

2673   12,402

Cheers!

sample.csv

Joe_Mako · Accepted Answer

How about the attached?

- Filter to remove Null record from sample file
- Text to Columns to split "source branch" on "-", space, or new line. It will ignore delimiters inside single quotes, and skip empty values (when there are consecutive delimiters)
- Multi-Row Formula to add the field "Field" because the field name and data are on different rows, this will fill the field name down the records
- Filter to remove the field name records, as they contain no data
- Multi Filed Formula to trim the single quotes and colons around the values
- Cross Tab to reshape and concatenate the data, resulting in one record per "target"