Splitting Text in One Cell to Four New Columns

[montyfern]

Greetings,

So stumped. I've tried power query splitting from columns to rows, dynamic array functions like textsplit, macros, more text splitting; everything under the sun. Their data sits in cell A3. They want to split the data into four columns with Col. A as Authors, Col. B Title, Col. C Journal, and Col. D Year. The problem is all the data sits in one cell & then some of sits in the same column. So adding any special characters like Mr. Excel's | & then splitting whacks it out. Also, in column A they have numbers of author citations, titles, totals and blank rows. It looks like this:

23​
Person A, Person B, Adams M, Person C, Person D, Person M, Person E, Person F, Harrison-Moore S, Lopez ZY. An In Silico Analysis of Blah Blah Pre-Erythrocytic-Stage Antigens Interpreting Worldwide Genetic Data to Suggest Blah Blah. Microorganisms. 2022 May 25;11(1):1090. doi: 10.3390/microorganisms111111. PMID: 111111; PMCID: PMC22222.
2021​
Total: 23
No
Authors, title, journal, year

Thanks so much for any help!

 

[RICH937]

Have to love APA! Easiest way is to use "text to columns" in the data tab. you'll end up with 7 columns instead of just the 4 you're looking for. choose "delimited," and then "other." Put a "." in the box next to other, and it will convert every instance a period occurs into a new column. If you want to separate the date from journal info, you can repeat and just choose semicolon in sept 2. Then just delete the unneeded tabs. If you want to join the DOI back into one, use textjoin(".",,cell1:cell3). Hope that helps!

 

[RICH937]

Greetings,

So stumped. I've tried power query splitting from columns to rows, dynamic array functions like textsplit, macros, more text splitting; everything under the sun. Their data sits in cell A3. They want to split the data into four columns with Col. A as Authors, Col. B Title, Col. C Journal, and Col. D Year. The problem is all the data sits in one cell & then some of sits in the same column. So adding any special characters like Mr. Excel's | & then splitting whacks it out. Also, in column A they have numbers of author citations, titles, totals and blank rows. It looks like this:

23​
Person A, Person B, Adams M, Person C, Person D, Person M, Person E, Person F, Harrison-Moore S, Lopez ZY. An In Silico Analysis of Blah Blah Pre-Erythrocytic-Stage Antigens Interpreting Worldwide Genetic Data to Suggest Blah Blah. Microorganisms. 2022 May 25;11(1):1090. doi: 10.3390/microorganisms111111. PMID: 111111; PMCID: PMC22222.
2021​
Total: 23
No
Authors, title, journal, year

Thanks so much for any help!

Did that work?

 

[montyfern]

Have to love APA! Easiest way is to use "text to columns" in the data tab. you'll end up with 7 columns instead of just the 4 you're looking for. choose "delimited," and then "other." Put a "." in the box next to other, and it will convert every instance a period occurs into a new column. If you want to separate the date from journal info, you can repeat and just choose semicolon in sept 2. Then just delete the unneeded tabs. If you want to join the DOI back into one, use textjoin(".",,cell1:cell3). Hope that helps!

Ahh, that is MUCH better RICH 937. How simple! Made it sooo hard. Thanks for your invaluable input...