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
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.