Macro Help for Substrings

[[email protected]]

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

[Ardus Petus]

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

<[email protected]> a écrit dans le message de news:
[email protected]...
> 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
>

[Tom Ogilvy]

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


"[email protected]" 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
>
>

[[email protected]]

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

[[email protected]]

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