formula to extract text from different columns

[RAM72]

I have worksheet which need to extract data starting from column K,L,M and G and insert in columns J

normal split, extract from columns K, L and from G, the word 1L resulting in J 16*4*1L

The second split type extract from column M and G and insert in J resulting to 2*6*1.5l

Third type of split extract from column K and L but criteria column H code by default and assign 75 cl.

Looking a formula for these, I don't know if these can be done

 

[bosco_yip]

J33, copied down :

=IF(I33="WINES",K33&"*"&L33&"*"&IF(ISNUMBER(FIND(37.5,G33)),"37.5CL","75CL"),IF(I33="LIQUEURS",K33&"*"&L33&"*"&LOOKUP(2,1/FIND({50;70;100;"1L"},G33),{"50CL";"70CL";"100CL";"1L"}),""))

Regards
Bosco

 

[RAM72]

Thank you Bosco on the right track

Need a little adjustment, for those in column J in black font cell , the same principle applies as in column V for expected results those with * in column are a bit tricky .

Apologize for missing description

 

[bosco_yip]

Please see revised file.

Regards
Bosco

 

[RAM72]

thanks Bosco nearly, just a little bit of the tricky parts which I have put an autofilter for ease of understanding.

Let me explain there are some who need to be extracted from column M which represents the total quantity but comes in packs of 6, 10, 12,4 as example which is reflected in column G designation where I highlighted in red .

I will take as example row 25 item Shandy column K and L reading 2 and 10 and column M represents total quantity (2*10=20)

column K *L=M

but column G represents the description which shows packs of 10*25 CL in red so it represent in columns J take from column M total quantity M(QTY 20)

EXTRACT (10X25CL) from column G which is translated in column J

as 20*10*25CL all are highlighted in green column J

on sheets all items with logic false need an adjustment as per above explanation and in green columns V the expected results .

the others are goods

 

[bosco_yip]

thanks Bosco nearly, just a little bit of the tricky parts which I have put an autofilter for ease of understanding.

Let me explain there are some who need to be extracted from column M which represents the total quantity but comes in packs of 6, 10, 12,4 as example which is reflected in column G designation where I highlighted in red .

I will take as example row 25 item Shandy column K and L reading 2 and 10 and column M represents total quantity (2*10=20)

column K *L=M

but column G represents the description which shows packs of 10*25 CL in red so it represent in columns J take from column M total quantity M(QTY 20)

EXTRACT (10X25CL) from column G which is translated in column J

as 20*10*25CL all are highlighted in green column J

on sheets all items with logic false need an adjustment as per above explanation and in green columns V the expected results .

the others are goods

Please refer to attached 2nd revised file

Regards
Bosco

 

[RAM72]

Bosco bravo and thank you for your kind assistance , I am studying your formula, very tough but awesome , I never thought this would be possible with the tricky parts .
thank you

 

[Hui]

Ram72

Your post has been cross-posted, which means it has been seen on other websites. This is considered poor practice, as it can waste peoples time, which could be spent elsewhere, especially if you get a solution and don't notify us or vice cersa.

I encourage you to please read the site rules at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/

 

[RAM72]

Please refer to attached 2nd revised file

Regards
Bosco

Hello Bosco

Since a while , formula works well, but new litres conversion had been added .

At row 56 to 60 are description from 1L,1.5l,2L,5L(wines ), I have tried to modify but no success as incorrect arguments.

Could please assist

Thank you in advance

 

[Luke M]

@RAM72

You have cross-posted again, directly after Hui telling you not to do that. You've also stated a new question in an old thread. Please take a moment to review the forum rules before continuing. Then, open a new thread with your question (and provide links to cross posts)

Cross post:
https://www.excelforum.com/excel-fo...merging-formula-taking-cl-but-not-litres.html