Simple question - logic not working on String

[Farhana91]

Hi All,

Back with another question:

This needs to be dynamic:

jan2017Actuals

Feb2018PY

Mar2019Plan

I need these in three columns. The text to columns does not work since there is no delimiter.

Month  Year      Scenario

jan      2017      Actuals

Feb     2018      PY

Mar     2019      Plan

I tried the following: 

Month:left([Name],3)

Year : (RIGHT(LEFT([Name],7),4))

Scenario: (RIGHT(LEFT([Name],15),7))  DID NOT WORK!!!

Please Guide.

Regards,

Farhana

[MarqueeCrew · Accepted solution]

Hi @Farhana91,

I would use the RegEx tool and configure it as follows:

• Choose your field to parse
• Regular Expression

  • (\w\w\w)(\d\d\d\d)(\w+)

    I've simplified the expression so that you are looking for 3 word characters followed by 4 numbers followed by 1 or more word characters
• Output Method is Parse

Here's a picture of the workflow:

The workflow (v11) is attached.

Cheers,

Mark

P.S.  You can change the output field names in the parse tool, but since you'll also be adding a select to remove the original field I elected to make all of these changes in the select tool.

P.P.S.  How about a vote/star for our Analytic Excellence entry?  https://community.alteryx.com/t5/Alteryx-Analytics-Excellence/Excellence-Awards-2017-Adam-Rant-From-...

[ThizViz · Accepted solution]

You've got the right idea. And Alteryx, way to get text in the middle of a field is with the substring function. [Substring(String,start,length)]

So your second and third statements would be

Substring([Name],4,4)
and
Substring([Name],8,7)

In your case, your longest text on the right is 7 characters, but when I make this formula I often use 99 so it will pick up whatever is remaining in the field.

[patrick_digan · Accepted solution]

@Farhana91 Nothing wrong with @MarqueeCrew's solution (I also love regex). I just wanted to point out that the substring function may be an option for you as well. For your scenario field, you would use something like:

Substring([Name],7)

[Farhana91]

Thank you all!! All the solutions perfectly worked :)