RegEx separate into columns

[DanielCarro]

Hi Guys,

Need some help with RegEx formulation as I am struggling a bit with it.  I need to split the cell into different columns.

I have the following:

15 May 2024   AAA(01)             GB008     4,000              0.4971000           1,966.80     0.00    1,966.80

16 MAY 24     BBBBB.US@0.1   GB008    70,000          0.014500               1,015.00      0.00       1,015.00

16 December 2024  cc(US_CBS)  AU005           11,000        0.3751000           1,486.00    0.00    1,486.00

I need to following output:

15 May 2024	AAA(01)	GB008	4,000	0.4971000	1,966.80	0.00	1,966.80
16 May 24	BBBB.US@0.1	GB008	70,000	0.014500	1,015.00	0.00	1,015.00
16 December 2024	cc(US_CBS)	AU005	11,000	0.3751000	1,486.00	0.00	1,486.00

Much appreciated any assistance on this one.

Dan

[leonhekkert]

Cant you first split the date with something like '\d{2}.*\d{2,4} and then afterwards split the remainder with a normal text to columns with \s as a delimiter?

[DanielCarro]

No, I can't as sometimes, I have a space in the second column such as AAA (01) as an example

[davidskaife]

Hi @DanielCarro 

This would work; Use a Data Cleansing tool to remove all duplicate whitespace, then Regex to parse out the date, and the remaining data into two columns, then split the remainder on whitespace:

[DanielCarro]

Hi David,  in the second column in some cases I have a space in AAA (01)

[davidskaife]

@DanielCarro 

Updated to hopefully account for the changes

Regex now parses out the first four columns before splitting on \s the remaining. Assumption is the third column (GB008) will stay as two letters, 3 numbers

(\w+\s\w+\s\w+)\s(.+\s|.+\)|.+[@].+)\s(\u{2}\d{3})\s(.+)

[davidskaife]

The parsing of the second column is based on it ending with a close bracket, or it contains @ within the text. Further tweaking may be required depending on the data.

[DanielCarro]

Hi @davidskaife , I am trying to separate in a column what's on the left of GB00.. and AU005. How can I do that?

[davidskaife]

Hi @DanielCarro 

I'm not sure i follow? The Regex for splitting that out is included in the post and on the attached workflow. If you mean using Regex to split out prior to the column containing GB00/AU00 then i was unable to do that as the Regex for GB00/AU00 helps force the code prior to work, if that makes sense.

[DanielCarro]

Hi @davidskaife , so I have a few items like these:

USC 0.125% ID/LD GB00C4X55G22

USC 0.125% 30/01/2025 GB00C4X55G22

IPXTT GUSD 31/12/2025 GB00BPK62234

DECXRA GB0.01 GB0000764424

how could I extract the bit on the left until GB?