Replace 2nd instance of a string with another string based on a condition

[virendrajadhav]

Hi - Need help!! I have a series of strings as input with each line item assigned a different value as follows: 

Input	Value
A_UVWXYZ 1A_CH	1
A_VWXYZ 1A_CHD	2
A_WXYZ 1A_CHDH	3
A_TWXYZ 1A_CHDHH	4
A_XYZAB 1A_CK	5

And I need the output as follows: 

Output

A_UVWXYZ 1A_CH

A_VWXYZ 1B_CHD

A_WXYZ 1C_CHDH

A_TWXYZ 1D_CHDHH

A_XYZAB 1E_CK

Basically, I am looking to replace the every second instance (ONLY) of "A_" based on the assigned value as per the following conditions:

if value = 2, replace second instance of "A_" with "B_"

if value = 3, replace second instance of "A_" with "C_"

if value = 4, replace second instance of "A_" with "D_"

if value = 5, replace second instance of "A_" with "E_"

else "A_"

It is quite easy to do in excel with the "Substitute" and nested "IF" functions but I am not able to figure out the logic in Alteryx. Any help is greatly appreciated. 

Thank you!

[bhrmitra]

Hi  virendrajadhav,

You can achieve it using a single formula tool.

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks,

Abhra Mitra

[virendrajadhav]

Hi @bhrmitra, 

Thank you this is really helpful. However and I apologize I should have made it clear in the question, not all the inputs will have the substring "1A_" at 6th position. To elaborate please find the few input examples below: 

Input	Value
A_UVWXYZ 1A_CH	1
A_VWXYZ 1A_CHD	2
A_WXYZ 1A_CHDH	3
A_TUVWXYZ 1A_CHDHH	4
A_XYZAB 1A_C	5

I have updated my question with this as well, in case you need to reference the output. 

Thank you!

[bhrmitra · Accepted solution]

Hi,

Change the formula to REGEX_Replace([Input], "\d\u", Left(GetWord([Input], 1), 1) + [Character])  it should work.

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks!