Remove and move the value in the cell

[k3pineapple]

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

ID	Company	Product	Mapping	SubMapping
1	ABC	Apple	abcdefg	{ABC.abcdefg} {BCD.abcdefg}
2	CDE	Banana	cdfghij	{ABC.cdfghij^bkcdef}
3	FGH	Oranage	aaa bbb cc	{BCD.aaa bbb cc}
4	AAA	Berry	aaa ccc dd	{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}

Output

ID	Company	Product	Mapping	SubMapping	SubMapping_R	Header
1	ABC	Apple	abcdefg	{ABC.abcdefg} {BCD.abcdefg}	abcdefg	ABC
1	ABC	Apple	abcdefg	{ABC.abcdefg} {BCD.abcdefg}	abcdefg	BCD
2	CDE	Banana	cdfghij	{ABC.cdfghij^bkcdef}	cdfghij	ABC
2	CDE	Banana	cdfghij	{ABC.cdfghij^bkcdef}	bkcdef	ABC
3	FGH	Oranage	aaa bbb cc	{BCD.aaa bbb cc}	aaa bbb cc	BCD
4	AAA	Berry	aaa ccc dd	{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}	aaa bbb cc	ABC
4	AAA	Berry	aaa ccc dd	{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}	qwertyu	ABC
4	AAA	Berry	aaa ccc dd	{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}	aaa bbb cc	BCD
4	AAA	Berry	aaa ccc dd	{ABC.aaa bbb cc^qwertyu} {BCD.aaa bbb cc} {DEF.aaa bbb cc}	aaa bbb cc	DEF

[Yoshiro_Fujimori · Accepted solution]

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