Very Complex ??Need to adjust formula giving unexpected results with trim & substitute

[RAM72]

Hi All

Need to adjust formula in column B and C.description with articles of plastic see as below

22042190 WINES ES (SADC R539722) Total 22042190 WINES(SADC R539722) ES
39269090 ARTICLES OF PLASTICS CN Total 39269090 ARTICLESPLASTICS CN OF
40169990 ARTICLES OF RUBBER HK Total 40169990 ARTICLESRUBBER HK OF

With description of in the middle, is going in columns in column C and origin CN is going in columns B.
See annexed file in red bold, i am having trouble with all description where there WORD OF in the middle.

Can anyone help to solve

thanks
jean

 

[NARAYANK991]

Hi Jean ,

See your file now.

Narayan

 

[RAM72]

Hi Jean ,

See your file now.

Narayan

Hi

Just test it on another file

Thank you, great save me from a a very tedious task