Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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