How can i extract specific words from a cell?

[mikey42979]

Is there a formula i can use to extract specific words in a cell?

Hypothetical Example: Treat the below 3 lines as Column A, Rows 1,2 & 3. The words i would like to extract are the ones in bold text and not all of them will start with the word golf. Thanks in advance for your help

Joes 9 hole golf course
kyles 18 hole golf School
looking for golf lessons

[Jakobshavn]

Unclear.................

Do you want a formula that will return the contents of the cell with the last two words removed?

[mikey42979]

Unclear.................

Do you want a formula that will return the contents of the cell with the last two words removed?

It could be the last 2 words or the last 5,6,7 etc...Its different for each cell. I was hoping i can specify the exact text to extract from each cell but a formula to extract the last few words might work ill just have to modify the number of words for each cell.

[mikey42979]

I just attached a sample file, not sure if it will help any.

[Jakobshavn]

Here is a version that looks for "golf" and uses it for extraction

[mikey42979]

Here is a version that looks for "golf" and uses it for extraction

I need the opposite of that, all the words in the beginning need to go away but its not just the golf words i need to keep i also need to keep the +CC, +GC, +Country club and other variations.

[Jakobshavn]

So:

1. if "golf" is found in a cell, keep "golf" and everything following it
2. if "golf" is not found in the cell, keep everything.

[mikey42979]

So:

1. if "golf" is found in a cell, keep "golf" and everything following it
2. if "golf" is not found in the cell, keep everything.

Almost, these are all the terms i need to keep in each cell. Each cell contains just one of the terms below, I need to remove everything before each term regardless of whats before it so in the end all i will be left with is an excel sheet full of these terms. I have a total of 300,000 plaus cells with these terms in them.

+cc
+country +club
+gc
+golf
+golf +club
+golf +course
+golf +deals
+golf +discounts
+golf +links
+golf +rates
+golf +reservations
+golf +resort
+golf +schedule
+golf +tee +times
+golf +times
+green +fees
+greens +fees
+tee +schedule
+tee +sheet
+tee +time
+tee +times
Country club
gc
golf
Golf Center
golf club
Golf Course
golf deals
golf discounts
golf links
golf rates
golf reservations
golf resort
golf schedule
golf tee times
golf times
green fees
greens fees
Resort and Golf Course
tee schedule
tee sheet
tee time
tee times

[mikey42979]

Anyone have a solution?

[Jakobshavn]

I can do this with a macro (UDF).
Is this acceptable??

[mikey42979]

I can do this with a macro (UDF).
Is this acceptable??

Sure anything that works im grateful for, i dont know how macros work but i know how to copy paste them into the macro tool lol.

[Jakobshavn]

Great!

Here is a sample workbook with the User Defined Function (UDF) installed.

We can use this workbook to make sure the UDF is satisfactory before you attempt to install it in your workbook.

[mikey42979]

Great!

Here is a sample workbook with the User Defined Function (UDF) installed.

We can use this workbook to make sure the UDF is satisfactory before you attempt to install it in your workbook.

Gah i dont know what happened i opened the file and it had the macro warning, i enabled it then the macro box came up and the name of the macro was the name of another file i had open so i hit cancel then i closed the other file went back to developer clicked macro and its empty. Dont know where it went, i even tried to re download the file and it still has no macro in it. Sorry for being such a noob!

[mikey42979]

Wait i see this formula and it seems to work partially but although its searching the entire column N for a match its stopping at the first iteration of the word. So out of this word (Willowbrook golf deals) its only keeping golf when i need golf deals.


=extractt(A1,N$1:N$43)

[Jakobshavn]

You are correct. It stops at the first match. Do you want it to continue to the last match??

[mikey42979]

If by last match you mean full match then yes so for example if its (Willowbrook golf deals) i would need only (golf deals) not both (golf) & (Golf Deals).

[mikey42979]

..........

[Jakobshavn]

Here is the updated version

[mikey42979]

Here is the updated version

Ok this one is working but i dont undertsand how lol the formula looks identical to the first file? How would i install this fix into another workbook, you mentioned its a UDP but i dont see anything under macros?

[Jakobshavn]

Here is the latest UDF:

Please Login or Register  to view this content.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I

ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

[mikey42979]

Ahhh another problem it wasnt fully working in the file mikey2, its not grabing the +symbols before the words. Sorry for this im sure your frustrated by now.

[Jakobshavn]

I see the problem!

Row #29 fails to find the match because the comparison is case-sensitive. This means golf will match, but Golf will not.

There are two possible solutions to this:

1. make the comparison not sensitive to case
2. add Golf to the lookup list

Which would you like??

[mikey42979]

Well its not just row 29 not matching mine doesnt match rows 4 & 5 through 17 for example in row 4 my result is showing gc not +gc and 6 im only seeing golf instead of +golf +club as the result. And it would also be better not case sensitve thanks.

[Jakobshavn]

Row #4 should show gc and not +gc because gc is lower down in the look-up list.

[mikey42979]

Row #4 should show gc and not +gc because gc is lower down in the look-up list.

Ok i get it now i thought it was doing exact match, is it possible to find the exact match string and also not be case sensitive? So +gc matches only +gc and gc matches only gc?

[Jakobshavn]

It IS an exact match. The problem is that the item in row #4 contains BOTH gc and +gc

[mikey42979]

I guess it wont work for what i need then thanks for the effort.

[Maureen405]

What are the items with a +?

[mikey42979]

What are the items with a +?

Are you asking what the + means?

[mikey42979]

In the attached file i put a header on the column's and a brief explanation to help you guys understand better. Column B is an example of the results im trying to get thanks I hope its enough and easier to understand.

[mikey42979]

Anyone have a solution to this?