Search and replace or return values after specific text in Excel

[Maven4Champ]

I am tasked with a challenge - one for which I do not yet have an answer.

I have 213 rows in an Excel workbook. In the left column is an object name and in the right column is a long SQL string, basically a CREATE OR REPLACE VIEW statement including SELECT, FROM, and WHERE clause.

What I want to do is in Column C of the excel workbook, display just the text that appears after the word "FROM" and before the word "WHERE"

This will give me the tables/views/objects being referenced so that I may complete analysis of our database reporting object dependencies.

Is this possible in Excel? I pictured something like a FIND/REPLACE to find anything with "*FROM" where * is the wildcard and then replace it with nothing or NULL value so that it removes everything prior to the word FROM. I would do the same for "WHERE*" where it would remove everything after the word WHERE.

Is something like this possible in either VB, Excel, or any other Office tool? I am hoping to do it quickly and automatically rather than slowly and manually.

 

[ettienne]

Look at the Search() function in Excel to find the position of FROM and WHERE in the text.
Use the MID() function in Excel to extract what you need.

 

[PHV]

A starting point:
Range("C2") = Split(Split(Range("B2"), "FROM")(1), "WHERE")(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Maven4Champ]

PHV, can you drill down a bit further on this? Is this a standard Excel function or VB function and if so, how to implement?

Ditto to ettienne.

 

[Maven4Champ]

Scratch that....I put it in VB in a Sub and it works. One additional question though - How to do it for all rows at once using a range of cells?