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

How to extract unique IDs from a text field

Highlighted
Fireball

 

I want to pull out references to unique IDs from a Text field.  The text field can vary in length.  The IDs have a specific construct:  (7- numbers + 1 letter) or ( 7-numbers + a combination of 1-3 letters and numbers).  Here are a few examples (e.g., 0601102A, 0601102BR or 0601102D8Z) .  ***The ID always starts with a 0 or 1.***.  

 

How can I extract these into a new field that list the 1-n references separated by a comma.  Example below...

 

Text Field to SearchNew Desired Output Field

"Lorem ipsum dolor sit amet, suspendisse sodales vitae quisque urna vestibulum aliquip. Nec elit eget ornare suscipit, viverra fermentum posuere sit, porttitor dictum justo duis ut, id amet ut magnis pede. Sodales sollicitudin odio rerum erat, eros in, maecenas ac facere bibendum pede pede, metus sollicitudin justo volutpat nibh, voluptate at tellus donec nam pulvinar faucibus. 

 

Work in this project builds on the materials research transitioned from PE 0601102A and 0601104A. This work complements and is fully coordinated with PE 0602618A

(blah blah), PE 0602786A (blah blah), and PE 0603001A (blah blah).

 

Eu eget congue quis pretium aenean eget, hendrerit nulla, ultrices sed volutpat sed elit vitae ornare, occaecat ut. Lectus quisque nullam. Tellus semper bibendum ante lorem."

0601102A,0601104A, 0602786A,0603001A

 

 

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Hey @hellyars

 

Where's this text field coming from?

Which kind of input data are you using?

 

Cheers,

Fireball

Excel. It's just a text field. 

Alteryx Certified Partner
Alteryx Certified Partner

@hellyars

 

Here's the solution I thought.

 

Solution2.PNG

 

Although there are many ways to do this. Let me know if there are any doubts about it.

 

Cheers,

 

 

I'd suggest using a Regex tool in tokenise mode. You can use a summarise tool to concatenate the results;

2018-11-03_20-53-41.png

 

Sample attached

Fireball

Sorry, I thought I responded.  

 

This works perfectly.  I was worried that applying it to my real dataset would bog down the processing time, but it did not.

 

Many thanks.

Fireball

This also works.  It is more refined.  And, it seems to address my concern that the parse approach could impact workflow performance when working with my full dataset.

 

Thank you. 

Labels