1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by Winston618, Nov 7, 2018.

  1. Winston618

    Winston618 Member

    Messages:
    42
    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 (vb):
     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 (vb):
     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!

    Attached Files:

  2. pecoflyer

    pecoflyer Active Member

    Messages:
    258
    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
  3. Winston618

    Winston618 Member

    Messages:
    42
    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!
  4. pecoflyer

    pecoflyer Active Member

    Messages:
    258
    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
  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,005
    Try,

    In C1, copied down :

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

    Regards
    Bosco
  6. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,918
    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"})

Share This Page