Filter row with Reg Ex

[whiran]

I have a big file with a column name "Desc". In a single row there can be both (LL USED Or L/L USED) and (LL USED or L/L NOT USED).   I need to filtered out only all records with LL USED or L/L USED initially. There after I need to extract only (LL USED Or L/L USED) quantity  as it entitle for only above quantity only. other wise it should remain with given quantity in qty column. If the raw consist of Bulk we must not consider the raw even it consist LL USED or L/L USED.

Eg: 

(1505.50KGS L/LUSED ,198KGS L/L NOT USED) (100G,125G,400G,250G,300G,500G)

CANADA

1703.5

[jdunkerley79]

I would suggest:

Use a filter tool with expression:

REGEX_Match([Desc], ".*L/?L USED.*")

Then a Regex Tool in parse with expression (L/?L USED)

Have attached a sample

[vizAlter]

Hi @whiran — Just being curious, can attach the desired output file as well?

[whiran]

Thank you very much for you prompt reply. I need to fine tune the solution as below. 

Desc
6.75KGS L/L USED, 13.50KGS L/L NOT USED

if we find out both L/L used and L/L not used presented in same row. extract L/L USED quantity. In this case  

eg : 6.75KGS 

Will you able to update the Regular expression to extract above figure.

required out put mention above.

[whiran]

Here I'm sending the out put that I required. 

[vizAlter]

@whiran — Thanks for the file! That's what I thought.

[jdunkerley79]

Try:

The RegEx parse will extract the KGS part if present and put the Qty in if not.

Finally joining back to the original record to make the full table. If no LL USED the column will be null

Updated sample attached

[vizAlter]

@whiran — Hope attached the solution will be helpful, if not let me know.

If it resolves your query Please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution.  Thank you.

[whiran]

Can you explain the regular expression that you have used

(\d.+)(L/L\s*USED)(.*.)(L/L\s*NOT USED)

Some times  L/L USED is being entered as LL USED and L/L NOT USED as LL NOT USED as well (| LL\s*USED) how to tackle above instance . Please help me on this as well. I have shared the revised test and out put for your continence . 

[whiran]

Can you optimize the solution  as I given in the revised solution.