Forum Discussion
Excel Text Manipulation
Hi again Rimy,
I cannot follow your suggested solution. It’s the first time of I’ve heard of PowerQuery, PowerPivot, or DAX coding.
What I am looking for is a function or series of functions or something that I can plug into Cells E2 and F2 and then copy down the rest of the rows.
These Functions will look at the text in C2 and extract the Dog Breeds and Cat Breeds.
I have enclosed a revised spreadsheet. The data is now in a table. The ‘Before’ tab shows what I have now. The ‘After’ tab shows what I want.
Any thoughts would be appreciated.
Thanks, Joel
So, Joel...can I jump in again, recognizing that what you've got is what you've got, that it comes from a legacy system....can I ask how many records there are in that legacy system and whether or not it wouldn't be worth it to spend time to actually approach a total re-design of the fundamental layout, so that, as I suggested in my first response, there's one record per person per animal. It absolutely will take some time, and it may be possible to automate some of that, but I would venture to say that there's going to be a fair amount of "manual" cleaning up needed regardless of how you do the first pass.
I'd be happy to create a sample of a cascading set of data validation tables (although I should warn you that they'd require the most current version of Excel) that could be used.
And again, I'd point out the far greater flexibility of output from a radically re-designed table of data. So it also comes back to what your ultimate purpose is, how you intend to use the data in whatever "final" format it achieves.
By the way, the suggestions from Riny_van_Eekelen are excellent--his are--but if you like me are on a Mac, some of those are features not accessible to Mac users (sigh), no matter how much we might wish they were.
Joel_Bauer Understood what you wanted, but don't think you can achieve exactly that so easily in any other way.
