Replacing Text Values Between Commas Without Text to Columns

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!

Workflow

Tips and Tricks

Transformation

Interface Tools

Accepted answers

flying008

Hi,@AConcernedAnalyst

Dear, you just need only find replace at once.

录制_2022_04_22_08_03_39_241.gif

All comments

flying008

Hi,@AConcernedAnalyst

Dear, you just need only find replace at once.

录制_2022_04_22_08_03_39_241.gif

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703