split formula format litrage measurement need adjustment not giving expected results

[JEAN1972]

I have a worbook in which column A represents litrage products in various formats

Column B to E represents the split

if in litres column D and E will stay 1 litre

if in cl divde by 100 to represent the amount in litres

if in ml divide by 1000 to litres

5*6*1l
5*6*1L
5*6*1litre
5*6*1litre

5*6*20ml
5*6*30cl

But I having an issue with ML , ml, l, L not giving the the expected results in column D and E

formula used as below which starts from from column B and then drag to column E

Please Login or Register  to view this content.

[Bo_Ry]

Please try

B2:C2 copy down

=IFERROR(--MID(SUBSTITUTE($A2,"*",REPT(" ",50)),COLUMNS($B2:B2)*50-49,50),"")

D2

=IFERROR(-LOOKUP(1,-MID(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A2,",","."),"*",REPT(" ",50)),50)),1,{1,2,3,4}))/1000^ISNUMBER(SEARCH("m",$A2)),"")

E2

=IFERROR(-LOOKUP(1,-MID(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A2,",","."),"*",REPT(" ",50)),50)),1,{1,2,3,4}))/1000^ISNUMBER(SEARCH("m",$A2))/100^ISNUMBER(SEARCH("c",$A2)),"")

Copy all down

I can Put more If to make for 4 column use same formula if need.

[JEAN1972]

thank you worked as requested