+ Reply to Thread
Results 1 to 10 of 10

Search and extract key word on a cell

  1. #1
    Registered User
    Join Date
    02-08-2022
    Location
    Bangalore; India
    MS-Off Ver
    Office 365
    Posts
    7

    Search and extract key word on a cell

    Hello team,

    Is it possible in Excel to search exact word (Single or multiple word with no case sensitive scenario) from summary text available in cell A2 using key point provided in cell B2 and extract the result in cell C2. Please let me know.

    Thanks in advance.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,505

    Re: Search and extract key word on a cell

    Yes, probably.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    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.

  3. #3
    Registered User
    Join Date
    02-08-2022
    Location
    Bangalore; India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Search and extract key word on a cell

    Raw data :

    Summary Key text Result
    unable to download file download download
    unable to access file access access
    unable to access account access access
    account locked locked locked
    application issues/errors issues/errors application issues/errors
    login & pass login & pass login & pass
    reset pass pass pass
    unable to sign file sign sign
    login & pass login & pass login & pass
    Last edited by shukrp; 02-10-2022 at 01:14 PM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: Search and extract key word on a cell

    I'll work on this as the data in post #3 can be copied and pasted into an Excel worksheet without modification.
    The following works for all but the 5th result, which appears to have a different rational from the others:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    02-08-2022
    Location
    Bangalore; India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Search and extract key word on a cell

    Thanks JeteMc it worked very well

    Just need small help further please. I have modified few details in raw data where I need further help :

    Raw data :

    Summary Key text Result Modified
    unable to download file download download No
    unable to access file access access No
    unable to acess account access access Yes
    account locked locked locked No
    application issues / errors issues/errors issues/errors Yes
    login&pass login & pass login & pass Yes
    reset pass pass pass No
    unable to sign file sign sign No
    login &pass login & pass login & pass Yes

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,087

    Re: Search and extract key word on a cell

    You need to explain what the new column is meant to do...
    Glenn



  7. #7
    Registered User
    Join Date
    02-08-2022
    Location
    Bangalore; India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Search and extract key word on a cell

    New column is just to let you know in which row changes are made.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,087

    Re: Search and extract key word on a cell

    Where does the previously supplied formula NOT work?

  9. #9
    Registered User
    Join Date
    02-08-2022
    Location
    Bangalore; India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Search and extract key word on a cell

    Yes, it did work however to just make it more searchable I have updated raw data so in case we have few spell error or space error even then formula can search them.

    Thanks for asking and helping me.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: Search and extract key word on a cell

    This works for the spacing errors shown in post #5, however it doesn't help with the spelling error:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I have heard of something called 'fuzzy lookup' which might help, however I have never used it, so I can't say with any certainty.
    Note that I am letting the formula return a #Value error if the 'value_if_error' fails so that you can filter column C and find instances of spelling errors.
    Let us know if you have any questions.

+ 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. Marco; extract to Word and highlight constant search terms?
    By jp09 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2016, 04:26 AM
  2. Word - modify macro to loop search procedure and then extract pages as pdf
    By adamstarr12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2016, 06:47 AM
  3. Extract unique numbers from Word and search for them in Excel
    By Reen in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 07-08-2014, 04:28 AM
  4. Search for a word in a column and extract the word
    By Abishekar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-17-2012, 09:30 AM
  5. [SOLVED] vba to search word and extract following 3 words
    By sausis in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-30-2012, 03:39 AM
  6. Replies: 1
    Last Post: 01-25-2011, 10:50 PM
  7. Search for and extract word from a cell
    By hmatharo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-30-2007, 10:21 PM

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