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

Returning characters up to multiple different strings formats

Jesse_Riegel
7 - Meteor

Hey everyone,

 

I have a set of data with trades made. The description column has:

 

1) name of the stock traded
2) the transaction code in brackets
3) The words explaining the transaction code

4) Useless words at the end of the important stuff.

 

There are four types of transactions I care about and here is the correct format:

 

ABC PIPE Ltd. (SEL) Sell
ABC HUNGRY LTD (XSL) Cancel Sell
ABC SR2 5OCT17 (BUY) Buy
ABC SR2 5OCT17 (XBY) Cancel Buy

 

However there are a lot of values that have additional information that was brought in (via pdf) such as below:

 

ABC CORP (SEL) Sell Closing Cash Balanc
ABC EFF 5 31JAN19 (BUY) Buy Closing C
ABC CORP (SEL) Sell Page 4 of 6 Dac

 

Question:

1) Is there a way to remove characters after a key phrase (bolded above)? Ex. find "(SEL) Sell" or find "(BUY) Buy" then remove everything after?

 

I know that I could combine a "midstring" function and "Find" for ")" but there are different amounts of characters afterwards. Not sure how to do for multiple different strings. 

 

Thank you very much!

5 REPLIES 5
ponraj
13 - Pulsar

Here is the sample workflow for your case.  Hope this is helpful. 

 

workflowworkflowResultsResults

tom_montpool
12 - Quasar

I also used RegEx but via the Formula tools...

 

 

Ladarthure
14 - Magnetar
14 - Magnetar

Hi,

 

I may not get exactly what you needed. I haven't used Regex and just used basic formulas to do so, you could change depending on what you need and change it with a if functionif you want something more specific

Jesse_Riegel
7 - Meteor

Thank you! The only part missing is the calculation for the "Cancel Sell" or "Cancel Buy", and someone did it below. Thanks again I appreciate your time

Jesse_Riegel
7 - Meteor

Thanks so much Tom! Much appreciated for your time!

Labels