# Extracting word from text string after certain symbol

#### Winston618

##### Member
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:
Code:
`` 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:
Code:
`` 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

#### Attachments

• 26.5 KB Views: 7

#### pecoflyer

##### Active Member
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

##### Member
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

##### Active Member
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

##### Excel Ninja
Try,

In C1, copied down :

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

Regards
Bosco

#### shrivallabha

##### Excel Ninja
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"})