Excel Text Manipulation

%3CLINGO-SUB%20id%3D%22lingo-sub-2736020%22%20slang%3D%22en-US%22%3EExcel%20Text%20Manipulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2736020%22%20slang%3D%22en-US%22%3E%3CP%3EDevice%3A%20PC%2FWindows%2010%3C%2FP%3E%3CP%3EMicrosoft%20Office%20Home%20and%20Business%202013%3C%2FP%3E%3CP%3EExcel%20Product%20ID%3A%2000196-20943-85146-AA714%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20enclosed%20spreadsheet%20there%20is%20a%20column%20headed%20%E2%80%98Animals%E2%80%99.%3CBR%20%2F%3EEach%20row%20contains%20the%20name%20of%20a%20person%20and%2C%20in%20the%20Animals%E2%80%99%20column%2C%20their%20favorite%20animals.%3CBR%20%2F%3EDogs%20and%20cats%20are%20listed%20by%20breed%20and%20prefixed%20with%20%E2%80%98a-%E2%80%98%20or%20%E2%80%98b-%E2%80%98%20respectively.%3CBR%20%2F%3EIs%20it%20possible%20to%20break%20out%20dog%20breeds%20and%20cat%20breeds%20into%20their%20own%20columns%20while%20still%20keeping%20them%20under%20%E2%80%98Animals%E2%80%99%3F%3CBR%20%2F%3EThanks%2C%20Joel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2736020%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2736073%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Manipulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2736073%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1148049%22%20target%3D%22_blank%22%3E%40Joel_Bauer%3C%2FA%3E%26nbsp%3B%20asks%3A%26nbsp%3B%3CEM%3EIn%20the%20enclosed%20spreadsheet%20there%20is%20a%20column%20headed%20%E2%80%98Animals%E2%80%99.%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEach%20row%20contains%20the%20name%20of%20a%20person%20and%2C%20in%20the%20Animals%E2%80%99%20column%2C%20their%20favorite%20animals.%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDogs%20and%20cats%20are%20listed%20by%20breed%20and%20prefixed%20with%20%E2%80%98a-%E2%80%98%20or%20%E2%80%98b-%E2%80%98%20respectively.%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIs%20it%20possible%20to%20break%20out%20dog%20breeds%20and%20cat%20breeds%20into%20their%20own%20columns%20while%20still%20keeping%20them%20under%20%E2%80%98Animals%E2%80%99%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20no%20doubt%20is%20possible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20may%20I%20first%20answer%20your%20question%20with%20another%20question%20(or%20maybe%20two%20or%20three)%3F%3C%2FP%3E%3COL%3E%3CLI%3EWhat%20is%20your%20ultimate%20purpose%20here%3F%20How%20will%20this%20data%20be%20used%3F%3C%2FLI%3E%3CLI%3EAt%20what%20stage%20are%20you%20in%20the%20creation%20of%20the%20real%20database%20(this%20is%20obviously%20just%20an%20example)%3F%3C%2FLI%3E%3CLI%3EAre%20you%20open%20to%20a%20wholly%20different%20approach%3F%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20do%20I%20ask%20those%20questions%3F%20Because%20in%20general%2C%20it's%20a%20terrible%20design%20idea%20to%20lump%20all%20kinds%20of%20disparate%20types%20of%20data%20into%20a%20single%20cell.%20A%20terrible%20design%20idea!%20As%20proof%20of%20that%20strong%20statement%2C%20the%20very%20fact%20that%20you're%20coming%20and%20asking%20%22can%20I%20break%20these%20out%20into%20their%20own%20columns%3F%22%20makes%20my%20point%20for%20me.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20a%20LOT%20easier%20to%20take%20data%20from%20multiple%20columns%2C%20even%20multiple%20rows%2C%20and%20produce%20a%20report%20that%20puts%20them%20together%2C%20a%20LOT%20easier%20to%20do%20that%20than%20it%20is%20to%20take%20data%20all%20stuffed%20into%20a%20single%20cell%20and%20break%20it%20apart.%20Are%20there%20text%20manipulation%20functions%20that%20could%20be%20assembled%20to%20do%20that%20breaking%20apart%3F%20Yes.%20But%20they're%20not%20easy%2C%20and%20the%20more%20inconsistent%20and%20erratic%20that%20data%20is%20(e.g.%2C%20the%20spelling%20of%20%22Frnch%20Poodle%22%20or%20is%20it%20%22French%20Poodle%22--in%20your%20example)%2C%20the%20harder%20it%20is%20to%20make%20sense%20of%20things.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20IF%20you're%20open%20to%20re-thinking%20the%20approach%2C%20I%20have%20some%20ideas%3A%3C%2FP%3E%3CUL%3E%3CLI%3EHave%20one%20row%20per%20favorite%20per%20member--yes%20that%20means%20a%20LOT%20of%20rows%2C%20but%20stay%20with%20me%3C%2FLI%3E%3CLI%3EUse%20data%20validation%20to%20get%20the%20spelling%20of%20each%20breed%20consistent%3C%2FLI%3E%3CLI%3EIf%20you%20have%20the%20most%20recent%20version%20of%20Excel%2C%20you%20can%20even%20have%20two%20or%20more%20levels%20of%20%22cascading%22%20data%20validation%2C%20such%20that%20first%20you'd%20select%20among%20%22Jungle%2C%20Dog%2C%20Cat....%22%2C%20which%20selection%20would%20lead%20to%20a%20focused%20secondary%20list%20%22Elephant%2C%20Lion%2C%20Tiger%2C%20Zebra...%22%20or%20%22French%20Poodle%2C%20German%20Shepherd%2C%20Bulldog....%22%20or%20%22Persian%2C%20Siamese%2C%20American%20Wirehair....%22%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreating%20a%20database%20such%20as%20I've%20alluded%20to%20there%20would%20enable%3A%3C%2FP%3E%3CUL%3E%3CLI%3Elists%20of%20all%20members%20who've%20identified%20%22Elephant%22%20as%20a%20favorite%20(or%20whatever)%3C%2FLI%3E%3CLI%3Elists%20of%20all%20of%20John%20Smith's%20favorite%20dogs%3C%2FLI%3E%3CLI%3Elists%20of%20all%20of%20John%20Smith's%20favorites%20of%20all%20types%3C%2FLI%3E%3CLI%3Elists%20of%20all%20people%20who%20have%20%22Horses%22%20AND%20%22Zebras%22%3C%2FLI%3E%3CLI%3Eof%20lists%20of%20all%20people%20who%20have%20%22Horses%22%20AND%20%22Donkeys%22%3C%2FLI%3E%3CLI%3Eetc.%20%5Bas%20noted%2C%20having%20separate%20data%20elements%20makes%20it%20a%20lot%20easier%20for%20Excel%20to%20do%20wonders%20with%20data%20retrieval%5D%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2736114%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Manipulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2736114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1148049%22%20target%3D%22_blank%22%3E%40Joel_Bauer%3C%2FA%3E%26nbsp%3BI%20agree%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B(i.e.%20John)%20that%20collecting%20data%20as%20displayed%20in%20your%20example%20is%20sub-optimal.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20%3CU%3Epossible%3C%2FU%3E%20solution%20that%20combines%20PowerQuery%2C%20PowerPivot%20and%20a%20minimal%20amount%20of%20DAX%20coding%20(Windows%20only%2C%20I'm%20afraid).%20The%20output%20is%20not%20exactly%20as%20what%20you%20asked%20for%2C%20but%20it's%20close.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20this%20only%20works%20because%20I%20put%20a%20comma%20between%20%22Lion%22%20and%20%22a-Beagle%22%20on%20the%20animal%20listing%20for%20John%20Smith.%20If%20you%20can't%20guarantee%20the%20consistent%20use%20of%20the%20comma%20to%20delimit%20animals%2C%20it's%20going%20to%20break.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20said%20that%2C%20it's%20probable%20better%20to%20collect%20your%20data%20along%20the%20lines%20as%20explained%20by%20John.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2738184%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Manipulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738184%22%20slang%3D%22en-US%22%3EHi%20Mathetes%2C%20Sorry%20I%20am%20not%20open%20to%20a%20different%20approach.%20Data%20is%20coming%20from%20a%20legacy%20system.%20We%20are%20lucky%20to%20get%20what%20we%20have%2C%20Thanks%2C%20Joel%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2738210%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Manipulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738210%22%20slang%3D%22en-US%22%3EHi%20Riny%2C%20Yes%20the%20data%20is%20sub_optimal%2C%20but%20its%20what%20we%20have.%20Yes%20all%20'animals'%20will%20be%20comma%20delimited.%20I%20have%20many%20questions%2C%20but%20your%20arrived%20at%20solution%20looks%20very%20promising.%20I%20need%20to%20be%20able%20to%20implement%20it%20myself%20on%20real%20data.%20I%20will%20be%20back%20to%20you%20after%20I%20have%20googled%20a%20bunch%20of%20things.%20Thank%20you%20so%20much.%20Joel%3C%2FLINGO-BODY%3E
Occasional Contributor

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 name of a person and, in the Animals’ column, their favorite animals.
Dogs and cats are listed by breed and prefixed with ‘a-‘ or ‘b-‘ respectively.
Is it possible to break out dog breeds and cat breeds into their own columns while still keeping them under ‘Animals’?
Thanks, Joel

7 Replies

@Joel_Bauer  asks: In the enclosed spreadsheet there is a column headed ‘Animals’.
Each row contains the name of a person and, in the Animals’ column, their favorite animals.
Dogs and cats are listed by breed and prefixed with ‘a-‘ or ‘b-‘ respectively.
Is it possible to break out dog breeds and cat breeds into their own columns while still keeping them under ‘Animals’?

 

It no doubt is possible.

 

But may I first answer your question with another question (or maybe two or three)?

  1. What is your ultimate purpose here? How will this data be used?
  2. At what stage are you in the creation of the real database (this is obviously just an example)?
  3. Are you open to a wholly different approach?

 

Why do I ask those questions? Because in general, it's a terrible design idea to lump all kinds of disparate types of data into a single cell. A terrible design idea! As proof of that strong statement, the very fact that you're coming and asking "can I break these out into their own columns?" makes my point for me. 

 

It is a LOT easier to take data from multiple columns, even multiple rows, and produce a report that puts them together, a LOT easier to do that than it is to take data all stuffed into a single cell and break it apart. Are there text manipulation functions that could be assembled to do that breaking apart? Yes. But they're not easy, and the more inconsistent and erratic that data is (e.g., the spelling of "Frnch Poodle" or is it "French Poodle"--in your example), the harder it is to make sense of things.

 

So IF you're open to re-thinking the approach, I have some ideas:

  • Have one row per favorite per member--yes that means a LOT of rows, but stay with me
  • Use data validation to get the spelling of each breed consistent
  • If you have the most recent version of Excel, you can even have two or more levels of "cascading" data validation, such that first you'd select among "Jungle, Dog, Cat....", which selection would lead to a focused secondary list "Elephant, Lion, Tiger, Zebra..." or "French Poodle, German Shepherd, Bulldog...." or "Persian, Siamese, American Wirehair...."

 

Creating a database such as I've alluded to there would enable:

  • lists of all members who've identified "Elephant" as a favorite (or whatever)
  • lists of all of John Smith's favorite dogs
  • lists of all of John Smith's favorites of all types
  • lists of all people who have "Horses" AND "Zebras"
  • of lists of all people who have "Horses" AND "Donkeys"
  • etc. [as noted, having separate data elements makes it a lot easier for Excel to do wonders with data retrieval]

@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 Mathetes, Sorry I am not open to a different approach. Data is coming from a legacy system. We are lucky to get what we have, Thanks, Joel
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

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

@Joel_Bauer Understood what you wanted, but don't think you can achieve exactly that so easily in any other way.

@Joel_Bauer 

 

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.