Dynamic Rename Tool

[WeiXiang20010]

I am trying to rename a few columns name:

First Name -> (Secondary) First Name

Last Name -> (Secondary) Last Name

First Name and Last Name -> No change

First -> No change

Last -> No change

I used this expression:
REGEX_Replace(
REGEX_Replace(
[_CurrentField_],
"\bFirst \w{4}\b", "(Secondary) First Name"
),
"\bLast \w{4}\b", "(Secondary) Last Name"
)

But I encounter a few issues:

1. There is no parentheses appearing on my output, it is just "Secondary First Name" and "Secondary Last Name"

2. There is change on "First Name and Last Name" to "Secondary First Name and Secondary Last Name"

Is there any other way to get what I plan to have?

[alexnajm]

Are you expecting additional columns in the future to match this pattern in some way? Because I'd simplify it: 

IF [_CurrentField_]="First Name" THEN "(Secondary) First Name" ELSEIF [_CurrentField_]="Last Name" THEN "(Secondary) Last Name" ELSE [_CurrentField_] ENDIF

[flying008]

Hi, @WeiXiang20010 

FYI.

IIF([_CurrentField_] in ("First Name", "Last Name"), '(Secondary) ' + [_CurrentField_], [_CurrentField_])

[WeiXiang20010]

@alexnajm and @flying008 

Thank you, but I am expecting new excel inputs in future which I cannot control the templates which may contains column names like "First Name (xxx)" and "Last Name (xxx)". Hence I would like to use contains instead of =

[alexnajm]

Then you could add something like !Contains([_CurrentField_], "and") to help out. But if there a bunch of different structures to this column name that could come in, it's risky to build a RegEx statement.

You could try "^First Name.*$" and "^Last Name.*$" and use an IF statement with Regex Match instead of RegEx Replace - just another option

[flying008]

Hi,@WeiXiang20010 

So, maybe you need upload more data sample to get your idea.

IIF(REGEX_Match([_CurrentField_], '(First|Last)\sName(\s\(.*)?'), '(Secondary) ' + [_CurrentField_], [_CurrentField_])

[WeiXiang20010]

@alexnajm alright let me try

@flying008 I was planning to have something like this in the end. Where all the excels inputs will be rename according to the master table then merge together and then I will remove those unwanted columns. 

[flying008]

Hi, @WeiXiang20010 

Please put your want output results (data or screenshot ) to here.

[flying008 · Accepted solution]

Hi, @WeiXiang20010 

Maybe this is your want :

Spoiler

IIF(REGEX_Match([_CurrentField_], '(First|Last)\sName(\s\(.*)?'), '(Secondary) ' + REGEX_Replace([_CurrentField_], '(?<=Name)\s.*$', ''), [_CurrentField_])

(Secondary) First Name	(Secondary) Last Name
A	B
C	D
A	B
C	D

[WeiXiang20010]

@flying008 Thank you so much, this is the code that I am looking for!