• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula to extract text from different columns

Status
Not open for further replies.

RAM72

Member
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
 

Attachments

  • normal split.jpg
    normal split.jpg
    24 KB · Views: 17
  • second split.jpg
    second split.jpg
    26.6 KB · Views: 12
  • split text.xlsx
    25.9 KB · Views: 6
  • third split.jpg
    third split.jpg
    27.1 KB · Views: 13
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
 

Attachments

  • split text(1).xlsx
    26.6 KB · Views: 2
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 :confused:
 

Attachments

  • split text adjust.xlsx
    33.9 KB · Views: 4
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
 

Attachments

  • TRICKY PARTS.jpg
    TRICKY PARTS.jpg
    154.8 KB · Views: 2
  • split text adjust(1).Axlsx.xlsx
    42.7 KB · Views: 1
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
 

Attachments

  • split text adjust(2).xlsx
    36.6 KB · Views: 15
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 .
:):awesome: thank you
 
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
 

Attachments

  • split additional litres wines.xlsx
    44 KB · Views: 1
Status
Not open for further replies.
Back
Top