How can I extract the below FieldExtractPQ00859-10-PN Other GBP Spot 01-OCT-21PQ00859-10Reverses "PQ872-10-AS AutoReversal GBP Spot 31-OCT-21"15-NOV-2021 06:10:48PQ872-10PQ872-10-AS AutoReversal USD Spot 31-OCT-21PQ872-10Reverses "PQ832-09-AS AutoReversal USD Spot 30-SEP-21"15-OCT-2021 06:10:46PQ832-09PQ872-10-AS Miscellaneous USD Spot 31-OCT-21PQ872-10PQ01018-02-AS Miscellaneous USD Spot 28-FEB-22PQ01018-02WPL1178-07-MS Transfer USD Spot 31-JUL-22WPL1178-07WPL-FB-06092022-CURRENCY CLEANUP Primary Book Only Adj AUD User 1WPL-FB-06092022Reverses "WPL-FB-06092022-CURRENCY CLEANUP Non USD Transfer EUR User 1"29-SEP-2022 15:48:53WPL-FB-06092022WPL 220725 01 MR USD Cashbook Transfer USD Spot 01-JUL-22WPL 220725 01 WPL 2208015 01 MR GBP Cashbook Transfer GBP Spot 01-AUG-22WPL 2208015 01PQ902-11-AS I/C Reclass AUD Spot 15-OCT-21PQ902-11PQ00924-12-PN I/C Reclass USD Spot 21-DEC-21PQ00924-12PQ001045-03-AS I/C Reclass EUR Spot 31-MAR-22PQ001045-03

Data Cleansing help

How can I extract the below

Field	Extract
PQ00859-10-PN Other GBP Spot 01-OCT-21	PQ00859-10
Reverses "PQ872-10-AS AutoReversal GBP Spot 31-OCT-21"15-NOV-2021 06:10:48	PQ872-10
PQ872-10-AS AutoReversal USD Spot 31-OCT-21	PQ872-10
Reverses "PQ832-09-AS AutoReversal USD Spot 30-SEP-21"15-OCT-2021 06:10:46	PQ832-09
PQ872-10-AS Miscellaneous USD Spot 31-OCT-21	PQ872-10
PQ01018-02-AS Miscellaneous USD Spot 28-FEB-22	PQ01018-02
WPL1178-07-MS Transfer USD Spot 31-JUL-22	WPL1178-07
WPL-FB-06092022-CURRENCY CLEANUP Primary Book Only Adj AUD User 1	WPL-FB-06092022
Reverses "WPL-FB-06092022-CURRENCY CLEANUP Non USD Transfer EUR User 1"29-SEP-2022 15:48:53	WPL-FB-06092022
WPL 220725 01 MR USD Cashbook Transfer USD Spot 01-JUL-22	WPL 220725 01
WPL 2208015 01 MR GBP Cashbook Transfer GBP Spot 01-AUG-22	WPL 2208015 01
PQ902-11-AS I/C Reclass AUD Spot 15-OCT-21	PQ902-11
PQ00924-12-PN I/C Reclass USD Spot 21-DEC-21	PQ00924-12
PQ001045-03-AS I/C Reclass EUR Spot 31-MAR-22	PQ001045-03

Preparation

@Data_Alter, one option is to use Regex to pattern match and parse the text you want.

this regex for this works on the data you gave as an example:

(PQ.*?-[^-]*|WPL-\D+-\d+|WPL \d+ \d+|WPL\d+-\d+)

The pipe | separates the different pattens in the example. For instance, the first pattern I found was

PQ.*?-[^-]*

This matches with PQ then any characters before a dash then a dash then any character which is not a dash.

If you want to learn more about Regex the community has some really quick interactive videos on getting to grips with it here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20Data

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Should row 5 not be PQ872-10 just like row 2 and 3?

@Data_Alter, one option is to use Regex to pattern match and parse the text you want.

this regex for this works on the data you gave as an example:

(PQ.*?-[^-]*|WPL-\D+-\d+|WPL \d+ \d+|WPL\d+-\d+)

The pipe | separates the different pattens in the example. For instance, the first pattern I found was

PQ.*?-[^-]*

This matches with PQ then any characters before a dash then a dash then any character which is not a dash.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Yes you are right

Thank you so much, I was struggling with Regex.

No worries @Data_Alter 😅 it takes a while to get used to Regex. Have a good one!