Extracting word from text string after certain symbol

[Winston618]

I have the below data on 3 different cell and I would like to extract the text string Book, Booklet & CD from each cell.

Data:
A1 = 1 sound disc (ca. 17 min.) : digital ; 4 3/4 in. + 1 book (31 p. : col. ill. ; 18 cm.)

A2 = 1 sound disc (ca. 14 min.) : digital ; 4 3/4 in. + 1 booklet

A3 = 1 sound disc (ca. 26 min.) : digital ; 4 3/4 in. + 1 CD

For the ease of my understanding, I split the formula into two parts.

First, I apply the following formula:

 B1 =(MID(A1,FIND("+ 1",A1)+4,256))

to locate the first cell and get book (31 p. : col. ill. ; 18 cm.)

Second, I use formula:

 C1 =LEFT(B1,FIND(" ",B1)-1)

to reference the first formula and get the word book successfully. However the formula doesn't apply to the second and the third cell and I have been working on it for hours.

Please find the attached file for better understanding

Am I missing something? Please help, thanks!

 

[pecoflyer]

Is this some kind of homework? Do you have to use formulas? If not you can use Data - Text to columns and use the +1 string and space as delimiters

 

[Winston618]

Haha, it's not homework, it's part of the data I need to deal with. It doesn't matter what I use, but formula is what I know. Can you explain more about your method? I don't quite understand, thank you!

 

[pecoflyer]

For some reason I am unable to open your file. (and I don't have XL here so my explanation might be a bit vague)
Select the column - Data - Text to columns - Delimiters - Check Space and add +1 next to "Others". Next - ( your results should now be in a same column in the wizard)
Select each of the columns you do not need and check " do not import".
Click "Finish"
Again if the number of words before the +1 string varies it will not work

 

[bosco_yip]

Try,

In C1, copied down :

=LEFT(B1,FIND(" ",B1&" ")-1)

Regards
Bosco

 

[shrivallabha]

In this case, you can use below alternative without support columns.
=LOOKUP(2^15,SEARCH({"1 book","1 booklet","1 CD"},A1,1),{"book","booklet","CD"})