Renaming data

[Chelseaa]

Hi all! i need help in renaming the data. It might be possible to do this using the LEFT or RIGHT tool? But i havent been successful with it, please advise!

Name

A1234 - Group APAC Team Finance

A2314 - Group APAC Team Human Resources

A4235 - Group EUROPE Management

A8237 - Group AMERICA Team Marketing

A8372 - Group AMERICA Business

Desired outcome:

Name	Desired name
A1234 - Group APAC Team Finance	Group APAC Team Finance (A1234)
A2314 - Group APAC Team Human Resources	Group APAC Team Human Resources (A2314)
A4235 - Group EUROPE Management	Group EUROPE Management (A4235)
A8237 - Group AMERICA Team Marketing	Group AMERICA Team Marketing (A8237)
A8372 - Group AMERICA Business	Group AMERICA Business (A8372)

Thank you all!

[Qiu · Accepted solution]

@Chelseaa 
Hope this is what you need.

[anjaliaithal]

hi Chelseaa,

I feel it is more of data transformation than joining. Please see if this helps

[Chelseaa]

@Qiu 

Thank you once again!! it works 🙂

[HomesickSurfer]

Hi @Chelseaa 

One formula, one tool solution:  

REGEX_Replace([Name], GetWord([Name], 0)+' '+GetWord([Name], 1)+' ', "")+' '+'('+GetWord([Name], 0)+')'

Results:

Group APAC Team Finance (A1234)
Group APAC Team Human Resources (A2314)
Group EUROPE Management (A4235)
Group AMERICA Team Marketing (A8237)
Group AMERICA Business (A8372)

[Qiu]

@Chelseaa 
You are welcome.

And thank you for the accept mark also.

[HomesickSurfer]

Hi @Chelseaa 

Does this work for you?

@Qiu @Do you see any challenges using my formula, opposed to the solution you offer?  Please advise.  Thank you

[Qiu]

@HomesickSurfer 
Thank you for asking, jut give my comments.

1. RegEx is not friendly for beginners in this case

2. Even it is RegEx, I simple replace it with Replace, and it works also

3. It is very elegant solution, and I like it.

[HomesickSurfer]

Thank you @Qiu