How to use the formula icon to check specifically the third character of a string?

[sehgaln]

I am trying to use the 'if' expression under the formula tool, but can't figure out how to check the third character of a string

[Kenda]

Hey @sehgaln!

I'm not sure exactly what you're looking to do with your if statement, but the following expression will return the third character of your string.

REGEX_Replace([Field1], "(..)(.)(.*)", "$2")

Hope this helps!

[sehgaln]

@BarnesK

If I need to do something like this: if third character in string is equal to 'X' then....

Would I still use RegEx?

[Kenda]

Yes @sehgaln. Here is a modified example from my first reply:

iif(REGEX_Replace([Field1], "(..)(.)(.*)", "$2")="c","TRUE","FALSE")

This looks to see if the third character is 'c' and returns TRUE if so or FALSE if not.

I added some spaces in the data to show that it still works.

[sehgaln]

This helps! What if I don't want it to say True or False? 

I want it so that if the third character is 'c' then set column to null

[Kenda · Accepted solution]

@sehgaln See if this gets you what you're wanting:

iif(REGEX_Replace([Field1], "(..)(.)(.*)", "$2")="c",null(),[Field1])

This checks if the third character is 'c' and makes the value null if so or back to the original value if not.

[sehgaln]

Yes, that is close! Thank you :)

[MarqueeCrew]

@sehgaln,

An alternative approach (@LordNeilLord, you might like this) to @Kenda's award-winning solution:

iif(Substring([Field1],2,1)="C",Null(),[Field1])

Instead of using a regular expression (pattern matching), if you use this string function you will get the same results.  There are always multiple ways to solve a challenge with Alteryx.  I chose this method because your search in position 3 is constant.  The substring function will run Faster than the Regex function.  I tested with 2 million rows and it solves the problem in less than 1/2 of the time (3.7 vs 1.5 seconds).

Cheers,

Mark

[sehgaln]

Thank you! How would I do multiple iif statements in the expression? Not nested iif, just multiple?

[MarqueeCrew]

@sehgaln,

More information in the question would be helpful.

iif(
Substring([Field1],2,1)="C" or Substring([Field1],4,1)="C"
,Null(),[Field1])

that's one way to have multiple conditions that end in the same result.

As a style, I generally have 1 condition in an IIF statement (2 is the exception).  If you explain the ask with a little more detail, I'd be happy to give you a quick response.

Cheers,

Mark