This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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".
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.