+ Reply to Thread
Results 1 to 3 of 3

Find a requested word within sentences and output those sentences elsewhere

  1. #1
    Registered User
    Join Date
    05-19-2014
    Posts
    1

    Find a requested word within sentences and output those sentences elsewhere

    Hello,

    I've got a sheet with hundreds of comments that range from 3 words to 300 words each. They are each contained within separate cells. I want to be able to type in a specific word that I'm looking for, and have excel output each entire comment where that word is found. I have set up a simple mock workbook that I will attach.

    Any thoughts?
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Find a requested word within sentences and output those sentences elsewhere

    So maybe A15:

    =IFERROR(INDEX(Sentences!$A$9:$A$12,SMALL(IF(ISNUMBER(SEARCH(Finder!$B$11,Sentences!$A$9:$A$12)),ROW($A$1:$A$4)-ROW(A$1)+1),ROW($A1))),"")

    This is an array formula, confirmed with Ctrl+Shift+Enter to exit the cell.

    Then copy downwards.
    Attached Files Attached Files
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Find a requested word within sentences and output those sentences elsewhere

    Another option might be:
    In the Sentences worksheet, have cell A1 = Who are you looking for?
    Have cell B1 = manual entry of name Max
    In Col B where the sentences are (starting B9), enter the following formula: =IFERROR(IF(SEARCH($B$1,A9)>0,A9,""),"") and then drag down. You can then see all of the sentences that contain the word Max. If you remove the blank rows between row 1 and 9, you can then filter cell B1 and see just those rows that have sentences. Don't know if this is an option but it would allow you to see everything in one worksheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count word found in column of sentences
    By [email protected] in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-17-2014, 08:27 AM
  2. gaps between first and second sentences
    By darek in forum Word Formatting & General
    Replies: 4
    Last Post: 04-29-2011, 08:55 PM
  3. Replies: 2
    Last Post: 12-16-2009, 07:56 AM
  4. Upper only first character in first word of sentences
    By cuongvt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-31-2007, 06:03 AM
  5. I need to put 4-5 sentences into one cell.
    By tysonfoggie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-18-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1