+ Reply to Thread
Results 1 to 16 of 16

Returning Substring when searching for text using a list

  1. #1
    Registered User
    Join Date
    08-31-2016
    Location
    Detroit MI
    MS-Off Ver
    2011
    Posts
    7

    Arrow Returning Substring when searching for text using a list

    Hello

    First post here. Im doing a formula that searches for text in a string via a list
    =SUMPRODUCT(--ISNUMBER(SEARCH(anger,C1)))>0

    That is my formula. It works fine but returns true or false. Is there a way to return the word it finds in the text? and what about if it finds multiple words?

    Any help would bne great

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Returning Substring when searching for text using a list

    You are checking for anger in C1 (your formula is missing quotes around the search word, without them it will always be FALSE)...
    Now are you wanting to know how many times this appears in that single cell?
    =(LEN(C1)-LEN(SUBSTITUTE(C1,"anger","")))/LEN("anger")

    Personally I would have the word anger in another cell outside to reference...say in D1, so that I could look at it without having to hard code the word in the formula...

    =(LEN(C1)-LEN(SUBSTITUTE(C1,Trim(D1),"")))/LEN(Trim(D1))

    Not sure if this is what you meant - post a sample file showing the scenarios you expect to see and results you wish to achieve.

    **Note - Substitute is case sensitive...so if your string says "Anger is anger and thats all there is to it", the cited solution would leave you with a count of 1 as it would remove only the all lower case reference of the word and find the character difference without that one instance.

    To get around this you could do the following (Unless you want case sensitive checks)
    =(LEN(PROPER(C1))-LEN(SUBSTITUTE(PROPER(C1),PROPER(Trim(D1)),"")))/LEN(PROPER(Trim(D1)))

    If anger is a named reference
    =(LEN(PROPER(C1))-LEN(SUBSTITUTE(PROPER(C1),PROPER(Trim(anger)),"")))/LEN(PROPER(Trim(anger)))

    By wrapping it in a PROPER() formula we force every word in the string to start with a capital letter and the rest of the word all lower. We do this to our referenced word to check as well to be sure that the operator (Currently you) does not accidentally drop the pinky to get something like anGer.

    Edit : Removed Case Sensitive issues as well as referenced search value from having leading or trailing spaces

    Test it out let me know your thoughts
    Last edited by ELeGault; 08-31-2016 at 11:51 AM. Reason: Added PROPER (CASE SOLUTION)
    -If you think you are done, Start over - ELeGault

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning Substring when searching for text using a list

    I'm assuming "anger" is a named range list of keywords?

    Show us SEVERAL examples of what could be in C1 and show us what results you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Returning Substring when searching for text using a list

    Quote Originally Posted by Tony Valko View Post
    I'm assuming "anger" is a named range list of keywords?

    Show us SEVERAL examples of what could be in C1 and show us what results you expect.
    Yea - I figured the same thing - but went ahead and left it open - In which case the example I listed should work still -

    It will only work if the goal is to find ...

    The Boy went to the store
    From
    Yesterday the boy went to the store and bought 3 candy bars

    If the named range says... boy store bought, than this will not work as it treats the reference as a string (As if it was one item)


    Example file would be most helpful!
    Last edited by ELeGault; 08-31-2016 at 11:54 AM.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Returning Substring when searching for text using a list

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Registered User
    Join Date
    08-31-2016
    Location
    Detroit MI
    MS-Off Ver
    2011
    Posts
    7

    Re: Returning Substring when searching for text using a list

    Yes ANger is a list range on a separate tab.

    I have a list of 1247 Words. I want to have excel "read" text and return the word it finds from that list.

    There is also the problem of the possibility of more than one of those words existing in some text

  7. #7
    Registered User
    Join Date
    08-31-2016
    Location
    Detroit MI
    MS-Off Ver
    2011
    Posts
    7

    Re: Returning Substring when searching for text using a list

    Quote Originally Posted by ELeGault View Post
    Yea - I figured the same thing - but went ahead and left it open - In which case the example I listed should work still -

    It will only work if the goal is to find ...

    The Boy went to the store
    From
    Yesterday the boy went to the store and bought 3 candy bars

    If the named range says... boy store bought, than this will not work as it treats the reference as a string (As if it was one item)


    Example file would be most helpful!
    http://www.filedropper.com/anger

    hopefully that link works
    Last edited by DetroitExcelGuy; 08-31-2016 at 12:39 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Returning Substring when searching for text using a list

    I am at work and that link is blocked for good reasons by the systems here -

    You can attach the file here by going advanced or take advantage of the image insert option and upload the file instead of an image -

    Hopefully someone else will be able to dive into this in the meantime

    Cheers

  9. #9
    Registered User
    Join Date
    08-31-2016
    Location
    Detroit MI
    MS-Off Ver
    2011
    Posts
    7

    Re: Returning Substring when searching for text using a list

    Quote Originally Posted by ELeGault View Post
    I am at work and that link is blocked for good reasons by the systems here -

    You can attach the file here by going advanced or take advantage of the image insert option and upload the file instead of an image -

    Hopefully someone else will be able to dive into this in the meantime

    Cheers
    anger.xlsx


    Lets see if this attachment worksanger.xlsx

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Returning Substring when searching for text using a list

    Try AlKey's formula from post #5

  11. #11
    Registered User
    Join Date
    08-31-2016
    Location
    Detroit MI
    MS-Off Ver
    2011
    Posts
    7

    Re: Returning Substring when searching for text using a list

    Quote Originally Posted by Jonmo1 View Post
    Try AlKey's formula from post #5
    Did and it works great. 1 issue I still face is what if multiple words from my list are in the string? I would I return each one of those words in a separate cell? Is there a way to nest the Lookup function to only look within the string after the word that was returned in the first look up function?

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning Substring when searching for text using a list

    Try something like this...

    Data Range
    A
    B
    C
    D
    1
    Joe Satriani - Big Bad Moon
    Bad
    Moon
    2
    3
    4
    5
    Minsk
    6
    Bad
    7
    Blip
    8
    Moon
    9
    Glop
    ------


    A5:A9 is the keyword list named "Anger".

    This array formula** entered in B1:

    =IFERROR(INDEX(Anger,SMALL(IF(ISNUMBER(SEARCH(Anger,$A1)),ROW(Anger)),COLUMNS($B1:B1))-MIN(ROW(Anger))+1),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across until you get blanks.

  13. #13
    Registered User
    Join Date
    08-31-2016
    Location
    Detroit MI
    MS-Off Ver
    2011
    Posts
    7

    Re: Returning Substring when searching for text using a list

    Still having having trouble with this one. Its returning the entire list. I attached my file could you please take a look.

    In the example the words "hate" and "bad" should be returned in separate cells adjacent to sample string.

    anger.xlsx

  14. #14
    Registered User
    Join Date
    08-31-2016
    Location
    Detroit MI
    MS-Off Ver
    2011
    Posts
    7

    Re: Returning Substring when searching for text using a list

    Quote Originally Posted by Tony Valko View Post
    Try something like this...

    Data Range
    A
    B
    C
    D
    1
    Joe Satriani - Big Bad Moon
    Bad
    Moon
    2
    3
    4
    5
    Minsk
    6
    Bad
    7
    Blip
    8
    Moon
    9
    Glop
    ------


    A5:A9 is the keyword list named "Anger".

    This array formula** entered in B1:

    =IFERROR(INDEX(Anger,SMALL(IF(ISNUMBER(SEARCH(Anger,$A1)),ROW(Anger)),COLUMNS($B1:B1))-MIN(ROW(Anger))+1),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across until you get blanks.
    Still having having trouble with this one. Its returning the entire list. I attached my file could you please take a look.

    In the example the words "hate" and "bad" should be returned in separate cells adjacent to sample string.

    anger.xlsx

  15. #15
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Returning Substring when searching for text using a list

    Maybe this
    Enter formula in D1 and copy across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v C D E F
    1 This is sample text. I hate when I can figure out formulas hate
    2 Some children have experienced abuse from foster parents abuse
    3 The detainee was charged with aggravated assault aggravated assault detainee

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning Substring when searching for text using a list

    A couple of problems.

    You didn't enter the formula as an array formula**.

    Also, it looks like some of your keywords are also substrings of longer words...

    bad - badger
    bad - badness

    So, we'll have to tweak the formula to account for that.

    Array entered**:

    =IFERROR(INDEX(anger,SMALL(IF(ISNUMBER(SEARCH(" "&anger&" ",
    " "&$A1&" ")),ROW(anger)),COLUMNS($B1:B1))-MIN(ROW(anger))+1),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Also problematic, the use of punctuation.

    If the string was: This is bad!

    The formula would not "see" the keyword "bad" due to the exclamation point.

+ 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: 4
    Last Post: 12-23-2013, 08:43 AM
  2. Searching for substring
    By mjohn99 in forum Excel General
    Replies: 8
    Last Post: 12-04-2013, 07:59 PM
  3. searching an array from a column set of text and returning a value..
    By bebop1337 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-07-2013, 05:06 PM
  4. Text searching within a cell, returning the nearest value.
    By starwarrior in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-29-2012, 09:38 AM
  5. Searching for a text string in a list and returning a value
    By Panda2012 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-02-2011, 10:17 PM
  6. Replies: 4
    Last Post: 08-12-2011, 06:57 PM
  7. Searching within substring text
    By MasterMonk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-05-2007, 01:16 AM

Tags for this Thread

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