String Functions - Returns a specified segment from a string

[Georgealex]

Issue - Need help in string function to be used

Sample data

Case -1  : 66-YY-ZZZZ-AAAAAA-123
Case -2 : MX-YY-AAAA-ZZZZZZ-596
Case -3 : 66|YYYYYYY|ZZZZZZZZ|AAAAAAAAA|1|12|11234|X

Requirement

I need to extract "AAAAAA" from Case-1 , "AAAA" from case-2 and "AAAAAAAA" from case-3. This is the mix of the data I have in my file

in other languages we can extract the 4th segment from case-1 after the '-' , 3rd segment from case-2 after '-' and in case 3, 4th segment after '|'.

How do i replicate it in Alteryx.

[Treyson]

Are you trying to extract any time that it says "A*" or are you looking for those specific spots in your string and want to extract whatever is there?

[Georgealex]

Clarification - it is not always 'A' . It is just the position. it would be a alphanumeric combination.

[Treyson]

I am sorry, but the other clarification I guess I would need is, is the position you need to find based on case number or are those just examples of record ids? Specifically, in any given string, how do I know if I need to look in the third or fourth spot?

[Georgealex]

That is a good question. Sorry it was not very clear

Case 1 and case 2 is when substring("string",1,2)  = '66' and (substring("string",3,1) = "-" OR substring("string",3,1) = "|")

Case 3 is when substring("string",1,2)  = 'MX'

[Georgealex]

update

That is a good question. Sorry it was not very clear

Case 1 and case 3 is when substring("string",1,2) = '66' and (substring("string",3,1) = "-" OR substring("string",3,1) = "|")

Case 2 is when substring("string",1,2) = 'MX'

[Georgealex · Accepted solution]

I figured out a round about solution using a combination of string functions

Step 1 :Use ReplaceChar(x, y, z): to replace special char with spaces

Step2 : Use GetWord(String, n) to extract the right word from the above result after spaces

[Treyson]

okay so the quickest solution I could come up with is attached. I just parse everything and then you would build your own logic around selecting the record (segment) that you need. I imagine you can do this with REGEX as well, but I am still a little fuzzy on which segments you are needing to grab. Is that excel syntax that you posted?

[Georgealex]

Thank you Treyson. I have downloaded and will review.

[riosjosh]

@Georgealex an alternative to using the String Functions in Alteryx would be to use RegEx tool. Very powerful tool that can parse out specific strings based on patterns. The following regular expression should accomplish your goal. Although I will say it is a bit predicated on your prefixes. If it is just though 3 or only a handful this solution will work. If there are many more then I would suggest break down the issue into smaller chunks and branching out your logic through multiple parses. I think you would get a better result and easier to follow logic in the more complex cases. BUT assuming only the three prefixes then this will do just fine. I agree with @Treyson that Regex101.com is killer and key to helping you through using RegEx.

(?<=66-)\w+-\w+-(\w+)|(?<=66\|)\w+\|\w+\|(\w+)|(?<=MX-)\w+-(\w+)

I've also attached a simple workflow. Hope it helps.