Macro Help for Substrings

All,

I am looking to rid myself of extra information in a column, and doing
so in a Macro

Here is what I have in column A

SELECT Column1, Column2 From Mydb.Myspace.My table Where Column1 =
'Hello"

I want to frind the FROM word (There is only one in the cell) and
delete all to the Left including the from

Then Find the WHERE (If it exsists) and delete all to the Right
including the WHERE

And I would end up with column A

Mydb.Myspace.My table

I have several examples of removing single char or commas from the
column, but not entire words.

Any ideas?

Doug

Assuming your text is in A4, enter the following formula in B4:
=TRIM(MID(A4,SEARCH("from",A4)+5,LEN(A4)-SEARCH("where",A4)))

HTH
--
AP

<douglascfast@hotmail.com> a écrit dans le message de news:
1147875520.907620.287350@i39g2000cwa.googlegroups.com...
> All,
>
> I am looking to rid myself of extra information in a column, and doing
> so in a Macro
>
> Here is what I have in column A
>
> SELECT Column1, Column2 From Mydb.Myspace.My table Where Column1 =
> 'Hello"
>
> I want to frind the FROM word (There is only one in the cell) and
> delete all to the Left including the from
>
> Then Find the WHERE (If it exsists) and delete all to the Right
> including the WHERE
>
> And I would end up with column A
>
> Mydb.Myspace.My table
>
> I have several examples of removing single char or commas from the
> column, but not entire words.
>
> Any ideas?
>
> Doug
>

Here is a demo from the immediate window showing how to do it.

You only need the final command:

s = "SELECT Column1, Column2 From Mydb.Myspace.Mytable Where Column1 =
'Hello'"
? s
SELECT Column1, Column2 From Mydb.Myspace.Mytable Where Column1 = 'Hello'
? left(s,Instr(1,s,"Where",vbTextCompare)-1)
SELECT Column1, Column2 From Mydb.Myspace.Mytable
?
mid(s,instr(1,s,"From",vbTextCompare)+4,Instr(1,s,"Where",vbTextCompare)-(instr(1,s,"From",vbTextCompare)+4))
Mydb.Myspace.Mytable

--
Regards,
Tom Ogilvy


"douglascfast@hotmail.com" wrote:

> All,
>
> I am looking to rid myself of extra information in a column, and doing
> so in a Macro
>
> Here is what I have in column A
>
> SELECT Column1, Column2 From Mydb.Myspace.My table Where Column1 =
> 'Hello"
>
> I want to frind the FROM word (There is only one in the cell) and
> delete all to the Left including the from
>
> Then Find the WHERE (If it exsists) and delete all to the Right
> including the WHERE
>
> And I would end up with column A
>
> Mydb.Myspace.My table
>
> I have several examples of removing single char or commas from the
> column, but not entire words.
>
> Any ideas?
>
> Doug
>
>

Sub ExtactText()
Dim nStart As Integer, nEnd As Integer
Dim txtString As String, txtSearch As String

'Next two lines create a test sample
cells(1, 1) = "delete all of this FROM column a WHERE delete all of
this."
txtString = ActiveCell

txtSearch = "FROM"
nStart = InStr(1, txtString, txtSearch) + Len(txtSearch) + 1

txtSearch = "FROM"
nEnd = InStr(1, txtString, txtSearch)

txtString = Mid(txtString, nStart, Len(txtString) - nStart - nEnd)

''Next line pastes results of test sample
ActiveCell.Offset(0, 1) = txtString
End Sub

Sorry, Sent the wrong one.

Sub ExtactText()
Dim nStart As Integer, nEnd As Integer
Dim txtString As String

txtString = ActiveCell

nStart = InStr(1, txtString, "FROM") + 5
nEnd = InStr(1, txtString, "WHERE")
txtString = Mid(txtString, nStart, nEnd - nStart - 1)

ActiveCell.Offset(0, 1) = txtString
End Sub