Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-15-2020, 06:06 PM
14spar15 14spar15 is offline Find first mention of word from list. Windows 7 64bit Find first mention of word from list. Office 2010 64bit
Advanced Beginner
Find first mention of word from list.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Find first mention of word from list.

Hello, In this attached database I have a list of words (Column G). I want to find the first mention of any of the column G words in the strings (Column A). That first mention would be in column B as shown. I have some experience with this (=ClearWords(A2,G$2:G$150) but I cannot figure out which function to use or how to relate this with this new situation where I don’t want to clear the word but show the first mention of any of the listed words. Thank you
Attached Files
File Type: xlsm Book2.xlsm (9.8 KB, 7 views)
Reply With Quote
  #2  
Old 12-15-2020, 10:08 PM
Purfleet Purfleet is offline Find first mention of word from list. Windows 10 Find first mention of word from list. Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I assume clearwords is a custom function someone wrote?

Well below is a formula with a named table, looks like it does what you are after

=MID(A2,AGGREGATE(15,6,SEARCH(Words,A2),1),SEARCH( " ",A2,AGGREGATE(15,6,SEARCH(Words,A2),1))-AGGREGATE(15,6,SEARCH(Words,A2),1))
Attached Files
File Type: xlsm Copy of Book2_Purlfeet.xlsm (12.8 KB, 7 views)
Reply With Quote
  #3  
Old 12-16-2020, 11:53 AM
14spar15 14spar15 is offline Find first mention of word from list. Windows 7 64bit Find first mention of word from list. Office 2010 64bit
Advanced Beginner
Find first mention of word from list.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Exact word match

Wow! That’s quite the formula. Thanks for the help here. How do the “15” and “6” come into this? Also, is there a way to make the words match exactly? If you look at Row 2 of the updated database I would like for the trigger to be “white” and not “brownish” as this is causing a problem.
Attached Files
File Type: xlsm Book2A_Purlfeet.xlsm (11.9 KB, 6 views)
Reply With Quote
  #4  
Old 12-16-2020, 12:48 PM
Purfleet Purfleet is offline Find first mention of word from list. Windows 10 Find first mention of word from list. Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

To be fair is mainly just repeating bits.

to stop partial matches use =MID(A2,AGGREGATE(15,6,SEARCH(Words&" ",A2),1),SEARCH(" ",A2,AGGREGATE(15,6,SEARCH(Words&" ",A2),1))-AGGREGATE(15,6,SEARCH(Words&" ",A2),1)).

The search part (SEARCH(Words&" ",A2),1) finds the word, the space finds the whole whole and not partials

aggregate 15 is the same as small but aggregate can deal with arrays, the 6 is telling it to ignore errors (i.e not matches). So if there are 2 matches this will find only the first one

the first AGGREGATE finds the number of the first character of the word we are looking for. (26)

the second AGGREGATE finds the number of the first space after the word we are looking for (31)

the third is just a repeat of the first AGGREGATE.

All this in the MID makes it Mid(a2,26,31-26)

Hope it helps
Reply With Quote
  #5  
Old 12-18-2020, 11:44 PM
14spar15 14spar15 is offline Find first mention of word from list. Windows 7 64bit Find first mention of word from list. Office 2010 64bit
Advanced Beginner
Find first mention of word from list.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

This seems to be doing what I need. I appreciate you taking the time to explain the formula so I can better understand it. Thank you
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find (highlight) two and more words in a list of 75k single words in Word 2010 Usora Word 8 05-29-2018 03:34 AM
VBA find word and create numbered list under it WarPiglet Word VBA 6 04-23-2018 05:06 AM
Purpose of the 'Mention' field Micromegas Outlook 0 04-11-2016 04:28 PM
Find and list Blank pages in Word document. ndandge Word VBA 4 11-29-2015 09:15 PM
Find first mention of word from list. Mail merge custom labels with double mention of addresses bng Mail Merge 6 05-30-2013 03:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft