Splitting complex contents of a cell across columns

[mark_farrell]

Hi, I would like to split the text for the reference below across columns. We have several hundred references that we need to break down for upload into a database. I can only get so far with the columns wizard and was wondering if it is crazy to think a formula could be written for this type of thing, or whether I should progress with the column wizard in stages.

All of the references follow the same pattern as far as format of punctuation goes, but obviously the number of authors can be more or less and the title of the article could vary but both will always end with a full stop. The title of the journal could be one word or many. I have indicated where splits are needed with the | symbol below. Any help would be appreciated. Many thanks

Allen KJ, Koplin JJ, Ponsonby A-L, Gurrin LC, Wake M, Vuillermin P, et al. Vitamin D insufficiency is associated with challenge-proven food allergy in infants. Journal of Allergy and Clinical Immunology 2013;131(4):1109-16 originally published online 27 Feb 2013 (IF 9.2) (HealthNuts) doi: 10.1016/j.jaci.2013.01.017

Allen KJ|Koplin JJ|Ponsonby A-L|Gurrin LC|Wake M|Vuillermin P|et al| Vitamin D insufficiency is associated with challenge-proven food allergy in infants.|Journal of Allergy and Clinical Immunology|2013|131|4|1109-16|originally published online|27 Feb 2013|9.2|HealthNuts|doi: 10.1016/j.jaci.2013.01.017|

[FDibbins]

Hi and welcome to the forum

It looks like most are separated by a comma? If so, could you use that as a delimiter in T2C?

[flesem25]

I agree the Text to Columns feature is your best bet.