issue with actual formula packsize drink split giving un expected output for specific data

[JEAN1972]

I have a worksheet which split drinks packing size

24*2*50CL that is splits by packs then per packs then original litrage then convert all original litrage to litres which is mandatory to . split to their individual columns


24 / 2 /50 /0.5


but I have an issue with 6*8*1L5 packsize giving me wrong output results 6 / 8 /1/ false expected results should be 6/8/1.5 /1.5.

formula used for third split giving 1 instead of 1.5

Please Login or Register  to view this content.

formula used in 4 split giving false instead of 1.5

Please Login or Register  to view this content.

[Olly]

You could use Power Query (Get & Transform Data). Format your source column as a table, then use:

Please Login or Register  to view this content.

[JEAN1972]

Hi Olly

Thanks for solution provided but I prefer a formula solution as the worksheet is very complex.

If anyone can assists in a formula solution

[Olly]

A complex worksheet is all the more reason to use Power Query

[José Augusto]

Hi

Try this approach
In F2 use
=SUBSTITUTE(
MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"M",""),"C",""),"L",IF(COUNTIF(A2,"*.*"),"",",")),FIND("*",SUBSTITUTE(A2,"*","|",1),1)+1,99),
".",
",")+0
Note: Probably you need delete the first SUBSTITUTE (in RED) and the change the red comma to decimal point like the formula:

Please Login or Register  to view this content.

In G2

Please Login or Register  to view this content.

[JEAN1972]

Try formula but getting #VALUE! in F, perphaps you could sent sample for teston my side unsuccessful.

Please Login or Register  to view this content.

[José Augusto]

Try formula but getting #VALUE! in F, ....

See the file

[JEAN1972]

Thanks working but issue with new G giving wrong answers when L in the middle showing 1 / 2/ 1 instead of 1.5 /2.5/ 1.5.

[José Augusto]

Hi

You must replace formula in F with NEW F

Use in F2 the following formula and copy down

Please Login or Register  to view this content.

See the file

[JEAN1972]

Hi Jose

Thanks now it clearer ,thank you for assistance.

[José Augusto]

You are welcome.