Replace or remove part of string based on another column

[AmirMoha]

Hi all, 

I'm really struggling here and I need help please

I have a column with Product names and in this column it has the product names plus the colour (ex; Blue, Black etc)

and I have another column that has the colours, 

in the product names column it has the colour name in two parts, at the first part and at the last part of the name, I want to keep the first part and remove the last part of the colour , examples are below

GIA | SILVER DIAMANTE SPIKE DETAIL HOOP EARRINGS SILVER

AURORA | LIGHT MOCHA ONE SHOULDER CAPE SLEEVE JUMPSUIT LIGHT MOCHA

I want to remove the highlighted parts of the colours but I am unable to do so as the spaces of the string are not the same ex; 5 spaces or 6, 

I have another column that has the colours 

I really appreciate your help

[MayilVahanan]

Hi

Try like below

Mapcolor:
Mapping
Load *, '~'&Color&'~' as ReplaceValue Inline
[
Color
SILVER
LIGHT MOCHA
];

Temp2:
Load ProductName, Trim(Replace(Left(TempField, Index(TempField,'~', 3)), '~', '')) as ExpectedResult;
LOAD *, MapSubString('Mapcolor', ProductName) as TempField INLINE [
ProductName
GIA | SILVER DIAMANTE SPIKE DETAIL HOOP EARRINGS SILVER

AURORA | LIGHT MOCHA ONE SHOULDER CAPE SLEEVE JUMPSUIT LIGHT MOCHA
];

[AmirMoha]

Hi Mayil, 

Thank you for your reply, but unfortunately this did not work as it removed some of the Product names, ex; WHITE BELTED FRONT TAILORED BLAZER WHITE, it changed it to WHITE BELTED FRONT TAILO , so it assumed that the Tailored the red as a colour

[AmirMoha · Accepted solution]

I have finally got this fixed,  with the below functions

LOAD *,
    Left(ProductName, Len(Trim(ProductName)) - Len(Trim(Colour))) as Product_name;