Filter row with Reg Ex

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

Revised_outPut.xlsx

Regex

Accepted answers

A possible optimised solution might be a formula:

IF REGEX_Match([Desc], ".*L/?L ?USED.*") THEN 
	IF REGEX_Match([Desc], ".*KGS L/?L ?USED.*") THEN
    	REGEX_Replace([Desc], ".*?([0-9.]+)KGS L/?L ?USED.*", "$1")
    ELSE
		[Qty]
    ENDIF
ELSE
    "0"
ENDIF

First, check if the Desc contains a variation of LL USED (any of LL USED, L/L USED, LLUSED, L/LUSED should be matched)

If it does and is preceded by a quantity in KGS extract this

Otherwise, if LL USED the return Qty

Finally, if no LL USED return 0

Updated sample attached

All comments

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

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

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.

test_output.csv

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

test_output.csv

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

Quick Links

Tags

This months top contributors