left mid right converting Excel formula to Alteryx

MikeFrancis1959

I have a Excel formula to take the "Original data" below and then change it to the "New data" and I tried to replicate this in Alteryx ( see below) but I get "unknown variable error" and not sure of the correct syntax, can someone help me correct this?

Thanks,

Mike

=IF(A1="","",IF(LEFT(A1,3)="ETA", "2023-"&MID(A1,5,2)&"-"&RIGHT(A1,2)&" ETA",A1))

(Excel)

Original data: ETA 05/26

New data: ETA 2023-05-26

IF [2nd GI]"","",IF LEFT([2nd GI],3)="ETA", "2023-" & Substring(ToString([2nd GI]),5,2) &"-"& RIGHT([2nd GI],2)&" ETA",[2nd GI]))

(Alteryx)

2nd GI format.yxzp

Workflow

Accepted answers

RSreeSurya

Hi,

The below formula should work.

if (IF [2nd GI]="" then '' else Left([2ND GI], 3) endif )='ETA' then 'ETA 2023-'+Replace(right([2ND GI],5),'/','-') else '' endif

Output:

All comments

RSreeSurya

Hi,

The below formula should work.

if (IF [2nd GI]="" then '' else Left([2ND GI], 3) endif )='ETA' then 'ETA 2023-'+Replace(right([2ND GI],5),'/','-') else '' endif

Output:

MikeFrancis1959

much, I was not close at all with my formula but now I can study the code to understand it and then be able to possibly use it for other workflows! Thank you so much for your help!

Mike

RSreeSurya

Welcome. 👍

If you are struck/facing any challenges, let me know.

Yuvaraj85

123-45-6789
999-99-9999
789-78-7896
Require if, mid and left formula to pull this complete SSN# where the 4 character is hypen else it should be blank

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703