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 search a field for a specific string and return what follows after the colon

Hello, 

 

I have a text field that contains multiple details (example below). I would like to query this field for the string "Item / Art" and return what follows after the semi-colon. It should return every instance of this. It will vary how many times the string "Item / Art" appears in the field. 

 

See field value below. It should return the following in one field separated by a comma: 1234567, 2345678, 9876543

 

FIELD VALUE

Description / Description : PRODUCT 123

Item / Art. : 1234567

UPC / CUP : 12345678912

Retail / P. det. : $1.87

Size / Format : 181g

 

Description / Description : PRODUCT 1234

Item / Art. : 2345678

UPC / CUP : 12345678912

Retail / P. det. :$3.41

Size / Format : 78g

 

Description / Description :PRODCT 789

Item / Art. :9876543

UPC / CUP :098765432109

Retail / P. det. :$1.98

Size / Format : 213g

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @newuser980345483 

 

Here's a solution with no Regex:

solution_noregex.PNG

 

- Filter Item/Art records

- Use Text to Columns to separate the colon 

- Use Formula Tool to trim the records

- Use Summarize tool to concatenate your values

 

WF attached on version 2018.4

 

Cheers,

Highlighted
Quasar

Just for the sake of it - a solution with Regex

 

2019-05-28_17-55-56.jpg

Hello - thanks for your response! 

 

I realize my initial question wasn't as clear as it could be. Please see attached. The field value I provided is one cell value. There can/will be multiple cells with similar structure, but could have any number of descriptions / item numbers, etc. within. I have attached this for clarity. 

 

The output would ideally return the following:

IDItem / Art
123

1234567, 2345678, 9876543

234

8765421, 8520369, 0658941, 3579510

345

3456789

885

1598745, 8521479

Alteryx Certified Partner
Alteryx Certified Partner

@newuser980345483 

 

Just with a few changes we can have your final output:

 

Changes.PNG

 

- Add a Text to Columns Tool before the filter - Use the /n delimiter and split to rows

- In the Summarize tool, group by ID

 

WF attached.

 

Cheers,

Based on both responses provided, I gave this a bit of a try and came up with the attached solution. 

Alteryx Certified Partner
Alteryx Certified Partner

@newuser980345483 

 

No problem, just keep in mind that the my last post gets to exactly what you need.

 

Cheers,

Labels