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 · Accepted solution]

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

[vizAlter]

@whiran — Try this one in the RegEx tool:

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

[whiran]

Thanks for your solution and the dedication

[whiran]

@vizAlter 

How the REGEX identify LL USED,LLOGO USED, L/L USED,LION LOGO USED, L/LOGO USED must fall to same category . Can you help me grab and genearalise all these combinations as L/L USED category .

[vizAlter]

@whiran — You can achieve this by the expression below:

// To find out the whole string, not partial

REGEX_CountMatches([Desc], "\bL.*L.*USED\b")

Enclosed the workflow for you.

[whiran]

@vizAlter 

Please check with the Input and the output files. When I apply your regexp , I hope there is something missing.

[vizAlter]

@whiran — Please take your Revised_ouPut.xlsx and share the workflow which you are testing on. Also, share the desired layout in Excel. Do you want the outputs in a single column for L/L USED only, not another column for L/L Not USED?

[whiran]

@vizAlter 

Here, I'm sending the solution that found with the community help. There might be more more such combination. Are there any other mechanism to do it more effectively