Text to Columns not using Wizard

[kourtnayking]

Hello: I have many citations that I'd like to separate in to different columns (author, title, publication, etc.), and it appears the Text to Column wizard won't address my specific needs because all the delimiters are different. And of course all the text strings are of different lengths, so I can't use the functions that address that. Is there a formula or function I could apply to this. Here is an example of a citation:

McClary, Ben H. "Washington Irving's Amiable Scotch Friends: Three Unpublished Letters to the John Gibson Lockharts." Studies in Scottish Literature. 4.( 1966): 101-104. p101. MLA International Bibliography. Gale. Bruccoli Clark Layman. 28 Jan. 2010

Thank you for your help!

[martindwilson]

sample workbook would help!

[kourtnayking]

Attached, Mr. Wilson. Thx!

[carsto]

I'm certain someone has a solution with far more finesse, but this appears to work, if I understand you correctly.

It seems although your delimiters are different, your data is consistant, at least the first 10 rows I checked.
Formulas assume:
- the Author ends at the first "
- the Title ends at the second "
- the Publication is the sentence following the Title
- the Remaining Info is all beyond the Publication

CAUTION: If your Title or Publication were to be 2 sentences (containing 2 periods) the formula would not return the desired results.

B2 =MID(A2,1,FIND("""",A2,1)-2)

C2 =MID(A2,FIND("""",A2,1)+1,FIND("""",A2,FIND("""",A2,1)+1)-LEN(B2)-3)

D2 =MID(A2,(LEN(B2)+LEN(C2)+5),FIND(".",A2,(LEN(B2)+LEN(C2)+5))- (LEN(B2)+LEN(C2)+5) +1)

E2 =MID(A2,LEN(B2)+LEN(C2)+LEN(D2)+6,LEN(A2))

copy formulas down