Splitting comma separated values in a field into multiple rows

[ccostello]

Hi all!

Hoping someone can help.

Please see below sample data:

Drink	Card or Cash	Price
Coffee, Tea, Coke	Card	80p
Coffee	Cash	80p
Coffee	Card	80p
Tea, Coke	Card	80p

I want to separate out the comma separated values into separate rows but keep the data in all other columns. I would want the above to look like the below:

Drink	Card or Cash	Price
Coffee	Card	80p
Tea	Card	80p
Coke	Card	80p
Coffee	Cash	80p
Coffee	Card	80p
Tea	Card	80p
Coke	Card	80p

What is the best way to do this please?

Please note also within the same data set have the same issue where some values are separated by '/'

Thanks so much!

[Blake · Accepted solution]

Hi @ccostello 

Use the text to columns tool but select "split to rows"

Although my screenshot doesn't show it, you can toss both delimiters in the tool. 

See the attached example and please mark this as solved if it accomplishes what you are trying to do.

[ccostello]

Thanks so much that was so quick and easy! Fixed my issue- appreciate it

[cff026]

Hey 

Is it possible to do this but also to split the value so as not to duplicate the cost - e.g. in the example provided above (copied again below):

Going from: 

Drink	Card or Cash	Price
Coffee, Tea, Coke	Card	80p
Coffee	Cash	80p
Coffee	Card	80p
Tea, Coke	Card	80p

Separate out the comma separated values into separate rows but keep the data in all other columns except for price which I would want to divide by the number of items I'm splitting. I would want the above to look like the below:

Drink	Card or Cash	Price
Coffee	Card	26.6p
Tea	Card	26.6p
Coke	Card	26.6p
Coffee	Cash	80p
Coffee	Card	80p
Tea	Card	40p
Coke	Card	40p

[atcodedog05]

Hi @cff026 

you might need additional processing for this.

[atcodedog05]

Hi @cff026 

Here is a workflow for the task. By taking the count and dividing the price

Output:

Workflow:

Hope this helps 🙂

Give a like if you dont mind 😀👍