Find a string from right before a speicifc string.

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

Preparation

Accepted answers

fmvizcaino

Hi @florayaoyao ,

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

Best,

Fernando Vizcaino

2020-02-17 Find a string from right before a speicifc string.yxmd

All comments

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

2020-02-17 Find a string from right before a speicifc string.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703