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

[sehgaln]

So I am adding a new column in the formula field, and I would like all the expressions to be under this new column I am creating. (I am a brand new Alteryx user, so I apologize if I am not clear).

1. If the third character in [Field1] = 'X', then set the value for the new column I just created to be null. 

2. If the third character in [Field1] = 'A', then combine 201 with the last digit of [Field1] and subtract 10. (need help with the subract portion)

3. If the third character in [Field1] = 'S', then combine 201 with the last digit of [Field1]

[sehgaln]

So I am adding a new column in the formula field, and I would like all the expressions to be under this new column I am creating. (I am a brand new Alteryx user, so I apologize if I am not clear).   1. If the third character in [Field1] = 'X', then set the value for the new column I just created to be null.  2. If the third character in [Field1] = 'A', then combine 201 with the last digit of [Field1] and subtract 10. (need help with the subract portion) 3. If the third character in [Field1] = 'S', then combine 201 with the last digit of [Field1]

[CharlieS]

@sehgaln

Here's an IF statement that will perform that operation for you.

IF Substring([Field1],2,1)="x" THEN null()
ELSEIF Substring([Field1],2,1)="A" THEN 201+tonumber(right([Field1],1))-10
ELSEIF Substring([Field1],2,1)="S" THEN 201+tonumber(right([Field1],1))
ELSE null() ENDIF

 If "201" is a field name, then use "[201]" in the IF statement. Otherwise, the second expression could be simplified to "ELSEIF Substring([Field1],2,1)="A" THEN 191+tonumber(right([Field1],1))"

*Edited for blunder-correction.

[MarqueeCrew]

@sehgaln,

Maybe you could create a sample .xlsx file with the before and after results expected?

@CharlieS read your requirements slightly differently than I did.

CBA999 does it become 200 or 2009?  (201 + 9) - 10 or (2019 -10)

CBS999 does it become 210 or 2019?

If the 2009 & 2019 results are what you are after (my read) then use:

IF	Substring([Field1],2,1)="A" THEN 
	ToString(ToNumber("201" + Right([Field1],1)) -10) ELSEIF 
	Substring([Field1],2,1)="S" THEN 
		"201" + right([Field1],1)
ELSE 
	null() 
ENDIF

You'll note that the 3rd position in the substring() function is actually 2 because this function is zero-based.  The first character is 0.  I also removed the search for "X" because null() is the default when A and S are not found.  I also capitalized the A and S.

Cheers,

Mark

[Kenda]

@sehgaln - 

As @MarqueeCrew stated, it depends on your interpretation of what you mean by "combine 201 with the last digit of [Field1]" but you could still use the "iif" statements if you wanted. Below is an example:

IIF(REGEX_Replace([Field1], "(..)(.)(.*)","$2")="X",NULL(),
IIF(REGEX_Replace([Field1], "(..)(.)(.*)", "$2")="A",ToString(ToNumber("201" + Right([Field1],1)) -10),
IIF(REGEX_Replace([Field1], "(..)(.)(.*)", "$2")="S","201" + Right([Field1],1),NULL())))

and of course, if you wanted to use the substring function, you could switch out the regex_replace here as well. 

So many great ways to accomplish one task!

[sehgaln]

@MarqueeCrew

In the example of CBA999 the result needs to be 2009. So 201, and then append the last digit on there, 2019, and then subtract 10, 2009. 

[MarqueeCrew]

Then you can use the solution that I provided above to get that result.

IF	Substring([Field1],2,1)="A" THEN 
	ToString(ToNumber("201" + Right([Field1],1)) -10) ELSEIF 
	Substring([Field1],2,1)="S" THEN 
		"201" + right([Field1],1)
ELSE 
	null() 
ENDIF

Cheers,

Mark

[sehgaln]

Thank you! How would I concatenate the 4th character of a string? Like "201" plus the 4th character in ABC27. So 2012. 

[Kenda]

@sehgaln I'm not sure if you want that in your if statement somewhere but either of these expressions will get you the 4th character in your string:

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

Substring([Field1],3,1)