How to correctly split column?

[HW1]

I have a column with values as below

RecordID	Value
19	Date Ref No Description PO Qty Price Total
20	01/02/21 583772.0 Empty 1.5 Cubic Metres (FL) - General Waste 1 $46.70 $46.70
41	05/02/21  586281.0 Empty 1100 Litre Bin - Paper Cardboard Recyclin 1 $5.18 $5.18
42	09/02/21 586867.0 Empty 1100 Litre Bin - Paper Cardboard Recyclin 2 $5.18 $10.36
106	11/02/21  588522.0 Empty 1100 Litre (Combo) - Container Recycling 10 $16.13 $161.30
166	17/02/21  590708.1 Empty 240 Litre - Container Recycling 1 $10.01 $10.01
260	20/02/21 592480.1 Empty 1100 Litre Bin - General Waste 1 $27.80 $27.80
489	01/02/21 583881.2 Empty 660 Litre (Combo) - Container Recycling 7 $12.90 $90.30
557	02/02/21 984332.0 Empty 1100 Litre Bin - Paper Cardboard Recyclin 1 $7.00 $7.00

I want it to be correctly split as:

RecordID	Date	Ref No	Description	PO	Qty	Price	Total
20	1/02/2021	583772	Empty 1.5 Cubic Metres (FL) - General Waste		1	$46.70	$46.70
41	5/02/2021	586281	Empty 1100 Litre Bin - Paper Cardboard Recyclin		1	$5.18	$5.18
42	9/02/2021	586867	Empty 1100 Litre Bin - Paper Cardboard Recyclin		2	$5.18	$10.36
106	11/02/2021	588522	Empty 1100 Litre (Combo) - Container Recycling		10	$16.13	$161.30
166	17/02/2021	590708.1	Empty 240 Litre - Container Recycling		1	$10.01	$10.01
260	20/02/2021	592480.1	Empty 1100 Litre Bin - General Waste		1	$27.80	$27.80
489	1/02/2021	583881.2	Empty 660 Litre (Combo) - Container Recycling		7	$12.90	$90.30
557	2/02/2021	984332	Empty 1100 Litre Bin - Paper Cardboard Recyclin		1	$7.00	$7.00

I am trying text to columns tool however, I am not able to parse it correctly.

Help will be much appreciated.

Thanks

[danilang · Accepted solution]

Hi @HW1 

You can do it with a couple of Regex Parse tools like this

The first one splits out Date, Ref No, Desc+PO+QTY, Price and Total.  The second one splits Desc+PO+QTY into Description and QTY.  The formula tool adds a blank PO field, since your data doesn't include any PO information

Dan