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
Solved! Go to Solution.
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
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
Perfect! thank you Fernando!
Could you share a little more how ".*,(.*)" works?
Appreciate it!
Flora