left mid right converting Excel formula to Alteryx
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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)
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Welcome. 👍
If you are struck/facing any challenges, let me know.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
