topic Custom Formula to Find a Specific Character in Alteryx Designer Desktop Discussions

Custom Formula to Find a Specific Character

lwolfram — Tue, 09 Apr 2019 13:21:52 GMT

I have an account field that is always 7 digits long. I need to find where the 5th digit is a 7. The number 7 could be anywhere in the account field, but I don't want to capture those. So the StartsWith, EndsWith and RegEx won't work for me. Does anyone have any other suggestions for how to locate these accounts.

So in the 4 examples below, I would only want to return the first 3 accounts.

4300701

4300755

4040788

4470000

Thank you!

Leigh

Re: Custom Formula to Find a Specific Character

CharlieS — Tue, 09 Apr 2019 13:32:23 GMT

Hi @lwolfram

Without RegEx, this expression could be used in a Formula tool:

IF Right(left(tostring([Account]),5),1)=='7' THEN 'true' ELSE 'false' ENDIF

Re: Custom Formula to Find a Specific Character

Thableaus — Tue, 09 Apr 2019 13:33:13 GMT

Hi @lwolfram

You could use this formula:

IF Substring(ToString([Field1]),4,1) = "7" THEN "Yes" ELSE "No" ENDIF

Substring function can get a start and a length to find strings.

Cheers,

Re: Custom Formula to Find a Specific Character

danilang — Tue, 09 Apr 2019 14:30:05 GMT

Hi @lwolfram

Just cause I've been trying to use the Regex hammer on everything that looks like a nail recently and also it's fun to do things differently from @Thableaus and @CharlieS (But don't tell them that)

REGEX_Match(tostring([Field1]), "\d{4}7\d{2}")

This matches a "7" as the 5 character

Dan

Re: Custom Formula to Find a Specific Character

lwolfram — Tue, 09 Apr 2019 19:03:40 GMT

Thank you all! Those are all great Solutions!