Removing duplicate and text to row or column.

[JingYih_Herbalife]

I have the following information all in one column. 

(15)King, Shobieng (Shobieng King) > (14)Vuan, Thang (Thang Vuan) > (13)Huynh, Vango (Vango Huynh) > (12)Hong, Anh (Anh Hong) > (11)Doping, Thoat (Thoat Doping) > (6)Huynh, Vango (Vango Huynh) > (5)Maitran, Nguyen (Nguyen Maitran) > (4)Doping, Thoat (Thoat Doping)

1st, I want to remove everything that is in a bracket and the symbol >

2nd, there are duplicate names such as Huynh, Vango & Doping, Thoat. 

I would like to view the result in one column such as below;

King, Shobieng

Vuan, Thang

Huynh, Vango

Hong, Anh

Doping, Thoat

Maitran, Nguyen

Can I do this using text-to-column or do I have to use RegEx?

Thanks in advance.

[Yoshiro_Fujimori]

Hi @JingYih_Herbalife ,

One way of doing this would be to use TextToColumns tool and RegEx_Replace function.

I hope this helps.

Workflow

Output

[Yoshiro_Fujimori]

Sorry, I forgot to add Unique tool.

[JingYih_Herbalife]

Hi @Yoshiro_Fujimori , any solution if I want to do it by having the results all in one row and multiple columns?

Thanks in advance. 

[Yoshiro_Fujimori]

@JingYih_Herbalife ,

You may want to use Cross Tab after Record ID tool.

Workflow

Output

[flying008]

Hi, @JingYih_Herbalife 

[JingYih_Herbalife]

Hi @Yoshiro_Fujimori 

For the RegEx replace formula, can you help with the formula to extract the name in the bracket instead?

Thanks in advance. 

[Yoshiro_Fujimori]

Here you go.

REGEX_Replace([Data], "\(.*\).*\((.*)\)", "$1")

You may want to check this site to test your RegEx.

https://regex101.com/

Goog luck.