Remove and move the value in the cell

Question

Hi,

I am trying to use this dataset to join another set by using  "SubMapping" field, but it's too dirty to use.

Therefore, will be much appreacited to help me getting this cleaned.

Input

IDCompanyProductMappingSubMapping1ABCAppleabcdefg{ABC.abcdefg} {BCD.abcdefg}2CDEBananacdfghij{ABC.cdfghij^bkcdef}3FGHOranageaaa bbb cc{BCD.aaa bbb cc}4AAABerryaaa ccc dd{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}

Output

IDCompanyProductMappingSubMappingSubMapping_RHeader1ABCAppleabcdefg{ABC.abcdefg} {BCD.abcdefg}abcdefgABC1ABCAppleabcdefg{ABC.abcdefg} {BCD.abcdefg}abcdefgBCD2CDEBananacdfghij{ABC.cdfghij^bkcdef}cdfghijABC2CDEBananacdfghij{ABC.cdfghij^bkcdef}bkcdefABC3FGHOranageaaa bbb cc{BCD.aaa bbb cc}aaa bbb ccBCD4AAABerryaaa ccc dd{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}aaa bbb ccABC4AAABerryaaa ccc dd{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}qwertyuABC4AAABerryaaa ccc dd{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}aaa bbb ccBCD4AAABerryaaa ccc dd{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}aaa bbb ccDEF

Yoshiro_Fujimori · Accepted Answer

Hi @k3pineapple ,

I could not keep the Original [SubMapping] value as it was lost in the 1st RegEx tool.

If you really need it, it can be retrieved by joining with original table with [ID].

I hope this may be of some help.

Workflow

RegEx (1st) configuration

RegEx (2nd) configuration