How to find a string from one table within a column of messy strings in another

I have a table with a column of data that shows strings of codes however the data is messy, rather than just being a list of codes, some of them have multiple codes, or have the word "and", some have other random text, some have commas, etc. The next column along beside these codes tells me what category these codes fall under.

I have a second table with a list of codes which are a clean list and have just one code in the cell and I need to obtain the category for each of them, but there are quite a few that can't be given a category because when I join them it's not finding a match between the list of clean codes and messy ones.

Ideally I want to Alteryx to take the string from the clean list, search through the entire column of the messy list and if it finds the code within any part of any row, returns the category, then moves to the next code, but I can't figure out how to do that.

Any ideas?

Datasets

Developer

Regex

Parse

Transformation

Accepted answers

jrgo

Nevermind my previous post... that tool could be used if your lookup was swapped.

You'll need to tokenize your codes to split them into separate row values, which the Regex tool is performing. The RegEx is looking for a pattern of 5 or more consecutive numeric characters. when found, it tokens into a new row and continues. Afterwards you can use the JOIN tool to to match up the two data tables.