VBA Locate Text String and Extract Nth Word

[Excel4444]

Hi!

I am trying to extract values from paragraphs. For example, there could be 1,500 characters equaling 200+ words. I would like to be able to locate a specific string of text (ie. "monday it will be") and then be able to extract the next two values 50 degrees - "monday it will be 50 degrees". I would like to be able to do this on a variable basis, not all paragraphs will be the same. My initial thought was to build a table that I would have to list the text strings I am looking for and the subsequent words, at least once, but then I could run the macro thereafter.

see attached.

thanks!

Workbook removed at OP's request

[Akuini]

Questions:
1. where are the text with the paragraphs?
2. could there be multiple match found? say there are multiple "monday it will be" in a paragraph.

[Rick Rothstein]

Does this UDF (user defined function) do what you want...

Please Login or Register  to view this content.

where From is the text you want to search, What is the text you want to find and HowMany is the number of words after the text you want to find to be returned. Note that if the What text cannot be found, the ExtractIt function returns the empty text string (""). Note that if there are more than one occurrence of the What text string, this function only finds the first occurrence of it.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ExtractItjust like it was a built-in Excel function. For your posted example, you would use this formula in cell D2...

=ExtractIt(D148,A2,C2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

[Excel4444]

Questions:
1. where are the text with the paragraphs?
ans: the text could be anywhere in a given paragraph
2. could there be multiple match found? say there are multiple "monday it will be" in a paragraph.
ans: yes, i guess multiple matches could be found but I would likely expand the text string to make uniquely identifiable as possible (if that makes sense).

[Excel4444]

to the proposed macro. I was able to "install" it and it worked when pointed at a single cell. Could I adjust to encompass a range of cells or even better the entire sheet?

OR first the vba would have to locate the string "monday it will be" and then use that cell for the ExtractIt?

[Excel4444]

I have been able to (with forum assistance) solve for looking into a range, but ran into something else. the macro brings the cell from the sheet with the data but only the cell address and not sheet2!A12, because I was hoping to reference that cell in the formula to return the words. attached is a sample with code.

Workbook removed at OP's request

[Winon]

Hello Excel4444.

I have changed the layout of the sample workbook a little. to make it easier for you.

Regards.

Workbook removed at OP's request

[Rick Rothstein]

attached is a sample with code.

I have a question about one of your examples. You have in cell A5 you have sub-1234 as the word to find and on your data sheet you have this sub-123456687 in cell C6. Your example did not show an Nth Word for it, but if you had shown a 1 there, what would you have wanted as a result... 56687 or a blank cell (actually "")? In other words, are you looking for whole word matches or partial word matches? Said another way, if you wanted to look for the word "other" and on the data sheet was the word brotherhood, should the word other within it be the found word? If the answer is no, then you will need someone who knows Regular Expressions to come along and answer your question as any pure VBA only code solution will be very slow for large amounts of text as the test to exclude embedded words can be time-consuming without using Regular Expressions.

[Winon]

Hello Excel4444.

I forgot to send you an updated sample workbook in Post #7.

It is now attached in that same Post #7.

Regards

[Excel4444]

No worries Winon, really appreciate it!

To answer Rick and follow up with Winon. if this can be done via vba, great, if not, formula will work. I was hoping to leverage the UDF ExtractIt pointed at column C, which has the cell address to return the words I need. So I wouldn't need to worry about Other in brotherhood, I would extend the string to include enough words to make it uniquely identifiable. I would like to be able type "monday it will be cold and" into A2 and then type the number 1 into B2 and have C2 return "50", because it went into sheet2 found "monday it will be cold and" which is part of the sentence "monday it will be cold and 50 degress", and it returned the first word from the end of sentence it went to find.

So I tried combing two approaching with the vba that gets the cell address and formula, so i can't get that to work, but if can all be done with VBA i'm open. thanks for the help.


updated workbook attached.

Workbook removed at OP's request.

[Rick Rothstein]

Two follow up questions...

1) For the text in cell A5, if you had put a 1 in D, what would you have wanted returned in Column F?

2) Is the word "and" what you actually wanted for a result in Column F for the text in cell A3 (given the text in cell B3)? I am asking because I think you would have wanted "and thank" as the result which, if so, would mean my ExtractIt code needs to be modified.

[Excel4444]

For cell A5 with a 1 in D would return nothing because there are no words after what’s found. And yes for it would “and thank”. My bad!

Thanks

[Excel4444]

thoughts of the follow ups?

[Rick Rothstein]

thoughts of the follow ups?

Sorry, I lost track of this thread. As to your question, if you are writing the address as shown in Column C, then just use the INDIRECT function on it to retrieve that cell on that worksheet's value. For example...

=ExtractIt(INDIRECT(C2),A2,D2)

[Excel4444]

so worries. worked like a charm.

thanks!