#1
|
|||
|
|||
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
|
#2
|
|||
|
|||
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)) |
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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
|
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 |
Mail merge custom labels with double mention of addresses | bng | Mail Merge | 6 | 05-30-2013 03:00 AM |