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.
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" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
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.
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:
=IF(ISNUMBER(SEARCH(B2,A2)),B2,"")
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.
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
You need to explain what the new column is meant to do...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
New column is just to let you know in which row changes are made.
Where does the previously supplied formula NOT work?
Yes, it did workhowever 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.
This works for the spacing errors shown in post #5, however it doesn't help with the spelling error:Formula:=IFERROR(IF(SEARCH(B2,A2),B2),IF(SEARCH(SUBSTITUTE(B2," ",""),SUBSTITUTE(A2," ","")),B2))
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks