RAW data seperation

I have a goodreads.com account to keep track of our growing paperback collection, but the data is not in a friendly form, so I want to mke it more user friendly by using that data in another excel book to format it better for reading on my iphone as a simple xlsx book. So the source date will be added to in the future.

RAW Title in column A is like the following 3 lines:

Brothers Majere (Dragonlance: Preludes, #3)
Infinity's Child
The Crusading Wizard (Wizard in Rhyme, #7)

Line 1 contains the Title: Brothers Majere, Series: Dragonlance, Sub-Series: Preludes, and # in the series 3.
Line 2 Contains only the Title: Infinity's Child
Line 3 contains the Title: The Crusading Wizard, Series: Wizard in Rhyme, and the # in the series 7, no Sub-Series

I would like to separate the data into 4 columns:
Title Series Sub-Series #

I suppose find and mid are involved but am unsure.
I do not want to edit the original file as this will change as I add books an export from the site.
Any aid would be nice, Thank-you

Vishesh's picture

Assuming you have office 2007

Assuming you have office 2007 or higher

If in Cell A1 you have 'Brothers Majere (Dragonlance: Preludes, #3)' or any of 3 values you mentioned

then

Title: =LEFT(A1,IFERROR(FIND("(",A1),LEN(A1)+2)-2)

Series: =IFERROR(MID(A1,FIND("(",A1)+1,IFERROR(FIND(":",A1),FIND(",",A1))-(FIND("(",A1)+1)),"")

Sub-Series: =IFERROR(MID(A1,FIND(":",A1)+2,FIND(",",A1)-(FIND(":",A1)+2)),"")

#:=MID(A1,IFERROR(FIND("#",A1)+1,LEN(A1)+2),IFERROR(FIND(")",A1),LEN(A1)+2)-IFERROR(FIND("#",A1)+1,LEN(A1)+2))

Thank-you this seems to have

Thank-you this seems to have worked. Fast response on your part.