community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Returning characters up to multiple different strings formats

Highlighted

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!

Quasar

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

 

workflow.PNGworkflowResults.PNGResults

ACE Emeritus
ACE Emeritus

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

 

 

Quasar
Quasar

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

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

Thanks so much Tom! Much appreciated for your time!

Labels