+ Reply to Thread
Results 1 to 14 of 14

Find multiple instances a word in a string of txt in a column and return values in columns

  1. #1
    Registered User
    Join Date
    01-12-2005
    Posts
    17

    Find multiple instances a word in a string of txt in a column and return values in columns

    Hi,
    I am trying to values in a column if a word appears in a string of text in another column
    for example

    Column A
    Row 1 Here is an apple
    Row 2 here is a banana
    Row 3 The apple is green
    Row 4 Strawberries are red
    Row 5 It is apple flavoured
    Row 6 Here is an orange

    Column B
    Row 1 £5
    Row 2 £12
    Row 3 £10
    Row 4 £7
    Row 5 £1
    Row 6 £17

    Column C
    Row 1 01/08/2019
    Row 2 25/03/2020
    Row 3 30/02/2019
    Row 4 05/11/2019
    Row 5 17/07/2020
    Row 6 22/11/2019

    I would like to produce a list of data showing all instances of apples, showing corresponding dates and amounts. I don't even know where to begin? Should I use a vlookup or a filter function?
    example worksheet attached. The cells highlighted in yellow is what I'm trying to achieve please
    Attached Files Attached Files
    Last edited by Swn-Y-Mor; 07-23-2020 at 09:25 AM. Reason: more information added

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    775

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    What version of Office do you have ?

  3. #3
    Registered User
    Join Date
    01-12-2005
    Posts
    17

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Office 365

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    775

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    A
    B
    C
    D
    E
    F
    G
    1
    Here is an apple
    £5.00
    8/1/2019
    apple
    £5.00
    8/1/2019
    2
    here is a banana
    £12.00
    3/25/2020
    apple
    £10.00
    2/27/2019
    3
    The apple is green
    £10.00
    2/27/2019
    apple
    £1.00
    7/17/2020
    4
    Strawberries are red
    £7.00
    11/5/2019
    5
    It is apple flavoured
    £1.00
    7/17/2020
    6
    Here is an orange
    £17.00
    11/22/2019



    E1=IFERROR(MID(INDEX($A$1:$A$6,SMALL(IF(ISNUMBER(SEARCH("APPLE",$A$1:$A$6)),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($A$1:A8))),SEARCH("APPLE",INDEX($A$1:$A$6,SMALL(IF(ISNUMBER(SEARCH("APPLE",$A$1:$A$6)),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($A$1:A8)))),5),"")


    Copy down


    F1=FILTER(B1:C6,ISNUMBER(SEARCH(E1,A1:A6)),"")
    Last edited by CARACALLA; 07-23-2020 at 10:52 AM.

  5. #5
    Registered User
    Join Date
    01-12-2005
    Posts
    17

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Thank you for assisting although I can't get that to work. It returns the full 6 rows of data

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    775

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    File attached

    works
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-12-2005
    Posts
    17

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Thank you so much for your help. I am trying to learn how and why it works now.

  8. #8
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    242

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Hi Swn-Y-Mor

    Since you have O365, attached is the Power Query solution (+ Search macro)

    Data > from tables bring in your source

    Create a search function

    Please Login or Register  to view this content.

    The source file invoke the above function

    in Excel, enter the text and create the Search Button (Macros) [Alt 11], the result will be displayed
    Attached Files Attached Files
    Christopher Yap

  9. #9
    Registered User
    Join Date
    01-12-2005
    Posts
    17

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Quote Originally Posted by CARACALLA View Post
    A
    B
    C
    D
    E
    F
    G
    1
    Here is an apple
    £5.00
    8/1/2019
    apple
    £5.00
    8/1/2019
    2
    here is a banana
    £12.00
    3/25/2020
    apple
    £10.00
    2/27/2019
    3
    The apple is green
    £10.00
    2/27/2019
    apple
    £1.00
    7/17/2020
    4
    Strawberries are red
    £7.00
    11/5/2019
    5
    It is apple flavoured
    £1.00
    7/17/2020
    6
    Here is an orange
    £17.00
    11/22/2019



    E1=IFERROR(MID(INDEX($A$1:$A$6,SMALL(IF(ISNUMBER(SEARCH("APPLE",$A$1:$A$6)),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($A$1:A8))),SEARCH("APPLE",INDEX($A$1:$A$6,SMALL(IF(ISNUMBER(SEARCH("APPLE",$A$1:$A$6)),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($A$1:A8)))),5),"")


    Copy down


    F1=FILTER(B1:C6,ISNUMBER(SEARCH(E1,A1:A6)),"")
    If I wanted to remove the hard coded "APPLES" from this, and instead look for a word shown in a particular cell eg H1 would this be possible? and if so, how?
    Thank you so much, I am learning so much

  10. #10
    Registered User
    Join Date
    01-12-2005
    Posts
    17

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Quote Originally Posted by bluesky63 View Post
    Hi Swn-Y-Mor

    Since you have O365, attached is the Power Query solution (+ Search macro)

    Data > from tables bring in your source

    Create a search function

    Please Login or Register  to view this content.

    The source file invoke the above function

    in Excel, enter the text and create the Search Button (Macros) [Alt 11], the result will be displayed
    That is very interesting! I must look for some video tutorials showing how you achieved that. Thank you

  11. #11
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    775

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Attached new file

  12. #12
    Registered User
    Join Date
    01-12-2005
    Posts
    17

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Quote Originally Posted by CARACALLA View Post
    Attached new file
    sorry, I can't see new the file

  13. #13
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    775

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Select fruit with dropdown list in K1
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-12-2005
    Posts
    17

    Re: Find multiple instances a word in a string of txt in a column and return values in col

    Quote Originally Posted by CARACALLA View Post
    Select fruit with dropdown list in K1
    That is very interesting. Thank you for taking the time and patience.
    I will now disect it and try and learn from you. Much appreciated

+ 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. Replies: 1
    Last Post: 01-31-2020, 11:27 PM
  2. Replies: 7
    Last Post: 05-11-2019, 12:38 PM
  3. Replies: 4
    Last Post: 10-27-2014, 04:07 PM
  4. Replies: 5
    Last Post: 10-15-2014, 03:38 PM
  5. [SOLVED] Find word in text string and return word adjacent to the left
    By eadamquinn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-06-2014, 03:22 PM
  6. Replies: 6
    Last Post: 02-06-2013, 07:45 PM
  7. is it possible to scan a string and eliminate multiple instances of a word?
    By SEBS_PARADISE in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-13-2011, 03:19 PM

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