Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Extract part of string

sergonza117
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,

Regards.

4 REPLIES 4
Hannah_Lissaman
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.

HomesickSurfer
12 - Quasar

Hi @sergonza117 

 

My approach below and attached.

 

1.PNG2.PNG

sergonza117
5 - Atom

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

HomesickSurfer
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.

Labels