Forum Discussion
Excel Text Manipulation
Device: PC/Windows 10 Microsoft Office Home and Business 2013 Excel Product ID: 00196-20943-85146-AA714 In the enclosed spreadsheet there is a column headed ‘Animals’. Each row contains the na...
Joel_Bauer I agree with mathetes (i.e. John) that collecting data as displayed in your example is sub-optimal.
Attached is a possible solution that combines PowerQuery, PowerPivot and a minimal amount of DAX coding (Windows only, I'm afraid). The output is not exactly as what you asked for, but it's close.
Note that this only works because I put a comma between "Lion" and "a-Beagle" on the animal listing for John Smith. If you can't guarantee the consistent use of the comma to delimit animals, it's going to break.
Having said that, it's probable better to collect your data along the lines as explained by John.
Hi Riny, Yes the data is sub_optimal, but its what we have. Yes all 'animals' will be comma delimited. I have many questions, but your arrived at solution looks very promising. I need to be able to implement it myself on real data. I will be back to you after I have googled a bunch of things. Thank you so much. Joel