• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting word from text string after certain symbol

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

Am I missing something? Please help, thanks!
 

Attachments

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

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"})
 
Top