Replacing Text Values Between Commas Without Text to Columns

[AConcernedAnalyst]

Hi all,

Wondering if anyone knew of a method where I can replace texts between commas? 

Example:

Say I have a column of strings (without quotes) of codes comma delimited with a variation in the amount of codes listed for each record:

"a12, a13, a14"

where each code corresponded to

a12 - apple

a13 - carrot

a14 - orange

What I would like to do is to replace each code with it's translation and keep the commas as is.  Meaning the string above should be changed to 

"apple, carrot, orange"

I know I can probably use a combination of the Text to Columns tool to delimit, Transpose tool to make a tall dataset, and use find and replace tool to find and replace tool, and then transform it back to the way it was, but that seems a bit convoluted. Looking to see if anyone happens to know a more straight forward approach.

Thanks!

[flying008 · Accepted solution]

Hi,@AConcernedAnalyst 

Dear, you just need only find replace at once.

[AConcernedAnalyst]

Hey

I think I wrote the example poorly. I should expand that I have multiple rows of varying amount of codes in each record. In my real life data set, there are over 400 possible codes (instead of just 3 as in my example) and each record has a random amount of codes in each set.

Expanding in my previous example, I would have 

record 1: a12, a13, a14

record 2: a13

record 3: a12, a14

record 4: a13, a12

.

.

.

And so on.

This would translate to:

record 1: apple, carrot ,orange

record 2: carrot

record 3: apple, orange

record 4: carrot, apple

.

.

.

and so on.

Looking to replace each individual code between the commas with corresponding values to those codes, keep the same original structure. Looking for the most direct approach (if one exists) whether through the built-in alteryx tools or if there is a python code tool option available.

[flying008]

@AConcernedAnalyst 

anyway, if you have the relation table for code and name, you can do it as above path.

[AConcernedAnalyst]

Yeah, you're right. When I had tried it out, it didn't work initially but that is because I had to adjust the size of the test string itself before the find and replace tool.

Thank you!