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!
Solved! Go to 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.
Thanks so much that was so quick and easy! Fixed my issue- appreciate it
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 |
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 😀👍