Extract part of string

[sergonza117]

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.

[Hannah_Lissaman · Accepted solution]

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]

Hi @sergonza117 

My approach below and attached.

[sergonza117]

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

[HomesickSurfer]

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.