How to search a field for a specific string and return what follows after the colon

[newuser980345483]

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

[Thableaus]

Hi @newuser980345483 

Here's a solution with no Regex:

- 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,

[ivoller]

Just for the sake of it - a solution with Regex

[newuser980345483]

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:

ID	Item / Art
123	1234567, 2345678, 9876543
234	8765421, 8520369, 0658941, 3579510
345	3456789
885	1598745, 8521479

[Thableaus · Accepted solution]

@newuser980345483 

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

- 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,

[newuser980345483 · Accepted solution]

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

[Thableaus]

@newuser980345483 

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

Cheers,