Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Extract part of string

5 - Atom

Hi all.


I've been stuck trying to extract N characters based on a delimiter, I need to extract two sets of characters of a string.


My string is like this one: "AN~iNeedThis_PR~iNeedThisToo_AV~someText_MK~anotherText_AR~"


The order of the AN~ and PR~ can change but they are always in the string; I used to work with other software using a formula like this: EXTRACT(MY_STRING,'AN~',1) , this trimmed everything after "AN~" and returned "iNeedThis_PR~iNeedThisToo_AV~someText_MK~anotherText_AR~" since AN~ is the delimiter and 1 the position, then I can nest this formula to something like this: EXTRACT(EXTRACT([MY_STRING],'AN~',1),'_',0) and the result is "iNeedThis".


Is there something similar within the Formula?.


Thanks in advance,


11 - Bolide

Hi @sergonza117 


You can use FindString([Field1], "AN~") to find the position of your delimiter. Remember that the first character position is 0. You can then combine this with functions Length, Left, Right and Substring to extract your data.


I have created an example for you in the attached. I think this is easiest (and clearest) to achieve in the two step version.

12 - Quasar

Hi @sergonza117 


My approach below and attached.



5 - Atom

Wow, thanks, I'm understanding the formula, this will be so helpful in other workflows as well.

12 - Quasar

Hi @sergonza117 @Hannah_Lissaman 


I too like the formulas, however, the use of text to column, transpose and crosstab will allow for multiple instances of the specific substrings, and; if not available.