Find a string from right before a speicifc string.

[florayaoyao]

Hi Alteryx gurus, 

I am trying to clean up a variable field which contains multiple fields separated by ",".    The field is not clean that I could not simply just use the "text to column"  by "," to parse the fields.  

For examples: 

",M6563982,4430270970,E178595-476-1"

",M3692717,TRANSFER,5100266124"

",D,RESISTOR".   

What I am looking for to return :   (Anything before the last comma) 

Step 1 to return: 

"E178595-476-1"  

"5100266123"

"Resistor"

Step 2;  if in step 1 return the value start with "E" like the first one "E178595-476-1" , then remove the "-1" (last dash and string following). The expected result: 

"E178595-476"  

"5100266123"

"Resistor"

Any help is very much appreciated! thank you!!

Best,

Flora

[fmvizcaino]

Hi @florayaoyao ,

I'm attaching an example showing how to do it with the help of a regular expression and a formula tool.

Take a look and let me know if this works for you.

Best,

Fernando Vizcaino

[florayaoyao]

Hi Fernando,

Thank you so much for the quick response. 

I run the formula through the entire dataset,  it didn't pick up the ones ending with "-2". "-3". etc.  Any idea to adjust that?  ( Remove any number after the last dash, not only "-1")

",M6568074,4430731873,E181099-016-2".   

Thank you!!

Flora

[fmvizcaino · Accepted solution]

Hi @florayaoyao ,

I've updated the workflow to remove all -digits as well.

Best,

Fernando Vizcaino

[florayaoyao]

Perfect! thank you Fernando! 

Could you share a  little more how ".*,(.*)"   works?     

Appreciate it! 

Flora