Closed Thread
Results 1 to 23 of 23

Search for multiple keywords within a single text cell

  1. #1
    Registered User
    Join Date
    10-25-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    21

    Unhappy Search for multiple keywords within a single text cell

    Hi,
    I am trying to find multiple keywords of text within a sentence contained in a range of cells.
    For example, in column F I have a series of job titles. In columns J and K I have 'Industry' and 'Focus'. I want to have excel search column F for keywords like "Highway", "Planning", or "Operations" and report in the Industry and Focus columns (i.e. if F2 contains the text Highway then Industry= Urban Roads; if F2 is equal to Planning then Industry is equal to Active Transport).
    I have been using the 'search' function with an IF logicial statement, but my fxn would become quite long once I enter all the different keyword possibilities.
    Thanks for your help.
    Nick

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search for multiple keywords within a single text cell

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-25-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Search for multiple keywords within a single text cell

    Okay, thanks for the heads-up.
    I am attaching a mock excel form showing how I would like the data to appear.
    Cheers
    Attached Files Attached Files
    Last edited by Paul; 10-26-2010 at 02:35 PM. Reason: Removed quote of full previous post

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search for multiple keywords within a single text cell

    In your sample you have comment:

    Would like to search column C for keywords like "Highway" or "Planning" or "Operations" and have column D and E automatically update with corresponding industry and focus information:
    But the first item has both "Highway" and "Operations" in it... so not sure which one you want?

    There are 2 methods I can provide. One to show you how to get a match for first listed word it comes across, and one for getting a match for last listed word it comes across.

    First, you need to create a table of values on the side.

    So say in M2:M10 you list the possible keywords.
    In N2:N10 you would list the corresponding Industries and in O2:O10 you would list the corresponding Focuses.

    Then in D2, to get the result for the first keyword it comes across (i.e. "Highway" in C2) use:

    Please Login or Register  to view this content.
    and copy down and to next column.

    To get the result for last keyword (e.g. "Operations" in C2) then.

    Please Login or Register  to view this content.
    copied down and to next column

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Search for multiple keywords within a single text cell

    Quote Originally Posted by NBVC View Post
    To get the result for last keyword (e.g. "Operations" in C2) then.

    Please Login or Register  to view this content.
    copied down and to next column
    I know I'm just repeating something I've said before but in the above case I'd opt for:

    Please Login or Register  to view this content.
    the 1/ISNUMBER is superfluous

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search for multiple keywords within a single text cell

    My first post of the day... and I already get jabbed...

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Search for multiple keywords within a single text cell

    Liar - I saw you post in the Water Cooler

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search for multiple keywords within a single text cell

    I meant real Excel-answering post.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Search for multiple keywords within a single text cell

    ah, sometimes I can't tell the difference ...
    (again: ducking for cover...)

  10. #10
    Registered User
    Join Date
    10-25-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    21

    Thumbs up Re: Search for multiple keywords within a single text cell

    Hi,

    Thanks for both of your help. Both functions seem to work fine.

    One more quick question: How do I eliminate the #N/A message when the search does not find any keywords within the search string? Can I have it report some other text value instead? I think this would involve the ISERR function, right?

    Thanks!
    Last edited by DonkeyOte; 10-26-2010 at 02:37 PM. Reason: removed unnecessary quote

  11. #11
    Registered User
    Join Date
    10-25-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    21

    Thumbs up Re: Search for multiple keywords within a single text cell

    Hi,

    Thanks for this function. It works great too.

    Can you explain the syntax so I can understand what it is telling excel to do?

    Thanks!
    Nick

    Quote Originally Posted by DonkeyOte View Post
    Please Login or Register  to view this content.
    the 1/ISNUMBER is superfluous

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Search for multiple keywords within a single text cell

    First off - I mistakenly removed your quote re: NBVC's formulae - apologies !

    Both LOOKUP functions (NBVC's and my slight adaptation) are based on SEARCH...

    The SEARCH function is applied 9 times - once per keyword against C2.

    SEARCH will either return a number (found) or an error (not found)

    In my example setting the criteria to be a very big number (9.99*10^307) means that given the nature of LOOKUP* the last number returned in the 9 value lookup_vector is the "result".

    That number is obviously not much use - so we apply an optional result_vector - which means that rather than returning the number from the lookup_vector the associated item from the result_vector is returned instead (ie the keyword)

    NBVC's approach is very similar only it has a couple of additional operations taking place that essentially mean the lookup_vector is populated with either 1 or #DIV/0!
    What is important to note is that the criteria value of 2 is still bigger than any numeric value in the lookup_vector and this ensures the last valid result is returned.

    Regards error handling - if using XL2007 just use IFERROR


    *LOOKUP - the function works as follows:

    - returns last item <= criteria from lookup_vector or associated item from result_vector if result_vector specified

    - LOOKUP assumes lookup_vector to be sorted in ascending order irrespective of reality

    - LOOKUP ignores any values in the lookup_vector not of the same data type as the criteria and this importantly includes error values
    (ie an error in the lookup_vector will not cause the LOOKUP to fail)
    Given the above it follows then that given the above if the criteria exceeds all known values of the same data type in the lookup_vector then given LOOKUP assumes the vector is sorted it will return the last item of the same data type (or associated value from result_vector where specified)

    For the reasons above LOOKUP is used widely to retrieve "last values" - ie last number, last text etc... from a range
    Last edited by DonkeyOte; 10-26-2010 at 02:54 PM. Reason: typos - repetition

  13. #13
    Registered User
    Join Date
    10-25-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Search for multiple keywords within a single text cell

    Perfect. Thanks so much for your help.
    Everything seems to be working fine at the moment.
    Cheers.

  14. #14
    Registered User
    Join Date
    10-25-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Search for multiple keywords within a single text cell

    Hi,
    I am having problems using the function (quoted) to get the last keyword in a string of text within a single cell.
    The function seems to pull out the first keyword it identifies (i.e. Operations) from the string. I would like to read a cell backwards (see example below) and have it grab the first keyword in that direction.

    Example:I wanted to grab the highest education achieved by an individual. Their educational achievements are listed as follows in a cell, call it B1:
    (cell contents for B1) Bachelor of Science, Master of Engineering, Doctorate of Philosophy (end of cell contents)

    So, i would want to grab the highest achievement using the appropriate keyword, in this case Doctorate, and report back in cell C1 "PhD".

    I hope that makes sense.
    Thanks for your help.



    Quote Originally Posted by NBVC View Post

    To get the result for last keyword (e.g. "Operations" in C2) then.

    Please Login or Register  to view this content.
    copied down and to next column

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search for multiple keywords within a single text cell

    The lookup list should be created in ascending hierarchical order...

    .. i.e. if Ph.D. Is the highest level achievable then Doctorate/Ph.D. should be listed last in your lookup range, M2:N10 (with no blanks).

    The Lookup looks for the last item in the lookup range that matches anything in your B2 entry.

  16. #16
    Registered User
    Join Date
    10-25-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Search for multiple keywords within a single text cell

    Hi guys,

    You have been invaluable resources so far in figuring out how to optimize a spreadsheet I am working on. Wondering if I can get a little more support. I suspect this answer would be fairly simple:

    I have a function that appears to be working for the most part, but I want to change the output in the event that Excel does not find a true statement for me.

    In the attached test worksheet, I would like to search column D for keywords like "Highway" or "Planning" or "Operations" and have column D and E automatically update with corresponding industry and focus information. If that information is not available in Column D, I would like to have Excel vlookup Column C and find the corresponding information in the lookup array in the worksheet called 'Toggles' .
    The function I created seems to work, but I don't want '0' entries if excel doesn't find the info it is looking for. Rather, I would like it to report 'Not Defined'.

    Thanks in advance for your help.

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search for multiple keywords within a single text cell

    Please post new questions in new threads. Also, no workbook attached.

  18. #18
    Registered User
    Join Date
    10-25-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Search for multiple keywords within a single text cell

    Quote Originally Posted by NBVC View Post
    Please post new questions in new threads. Also, no workbook attached.
    Hi,

    Will do.

    In the meantime, here is the workbook i forgot to upload.

    Cheers,
    nick.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    01-31-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Smile Re: Search for multiple keywords within a single text cell

    HI
    I need some help here
    I need to search the keyword for articles, and it will return the name of the articles as a result.
    here I attach the file as a reference. I have been using many excel formula but still fail.
    thank you for any help or suggestion.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,160

    Re: Search for multiple keywords within a single text cell

    Auni,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  21. #21
    Registered User
    Join Date
    01-31-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Search for multiple keywords within a single text cell

    Hi Arlu
    Thank you for remind me. Sorry for the post. I will do as you told.

  22. #22
    Registered User
    Join Date
    06-30-2020
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    2

    Re: Search for multiple keywords within a single text cell

    11790 SW Barnes Rd.Ste. 390Portland, OR 97225
    11850 Mayfield Road, Suite 3Chardon, OH 44024
    119 University BoulevardSuite DHarrisonburg, VA 22801
    11910 S Memorial DriveBixby, OK 74008
    11921 Lakeside DriveFishers, IN 46038
    11940 Quivira RdOverland Park, KS 66213
    12 Park StreetWaterville, ME 04901
    120 Cross RdBasking Ridge, NJ 07920

    I have complete address in one column we need saparte Street, city ,state, and zip , can any one help on this

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,899

    Re: Search for multiple keywords within a single text cell

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

Closed Thread

Thread Information

Users Browsing this Thread

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

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