Hello everybody,

Please kindly tell how to extract the key word "popular " by VBA function,
say Mid, how to:

Dim theString as String
theString = "Jeans are popular among the young."

Thanks & Regards.

What do you want, to check that it is there, or its position, or what?

myVar = Instr(1,"Jeans are popular among the young.","popular")

returns its position assuming it exists

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Hello everybody,

Please kindly tell how to extract the key word "popular " by VBA function,
say Mid, how to:

Dim theString as String
theString = "Jeans are popular among the young."

Thanks & Regards.

Thanks Bob,

Sorry for my expressions being not clear, say:

"c:\Files\Excel8\peter.xls"
"c:\Files\Excel888\michelle.xls"
"c:\Files\Excel88\may.xls"

I want to extract the *.xls name in a For Loop.

Thanks & Regards.



"Bob Phillips" wrote:

What do you want, to check that it is there, or its position, or what?

myVar = Instr(1,"Jeans are popular among the young.","popular")

returns its position assuming it exists

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Hello everybody,

Please kindly tell how to extract the key word "popular " by VBA function,
say Mid, how to:

Dim theString as String
theString = "Jeans are popular among the young."

Thanks & Regards.

You should work from the back then

Dim myVar As String

For i = Len(theString) To 1 Step -1
If Mid(theString, i, 1) = "\" Then
myVar = Mid(theString, i + 1, 99)
Exit For
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Thanks Bob,

Sorry for my expressions being not clear, say:

"c:\Files\Excel8\peter.xls"
"c:\Files\Excel888\michelle.xls"
"c:\Files\Excel88\may.xls"

I want to extract the *.xls name in a For Loop.

Thanks & Regards.



"Bob Phillips" wrote:

What do you want, to check that it is there, or its position, or what?

myVar = Instr(1,"Jeans are popular among the young.","popular")

returns its position assuming it exists

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Hello everybody,

Please kindly tell how to extract the key word "popular " by VBA
function,
say Mid, how to:

Dim theString as String
theString = "Jeans are popular among the young."

Thanks & Regards.

Hi
just to add to Bob. Depending on your Excel version (Excel 2000+) you may
also have a look at InStrRev

e.g.
i=instrRev(theString,"/")
myVar = Mid(theString, i + 1, 99)

"Bob Phillips" wrote:

You should work from the back then

Dim myVar As String

For i = Len(theString) To 1 Step -1
If Mid(theString, i, 1) = "\" Then
myVar = Mid(theString, i + 1, 99)
Exit For
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Thanks Bob,

Sorry for my expressions being not clear, say:

"c:\Files\Excel8\peter.xls"
"c:\Files\Excel888\michelle.xls"
"c:\Files\Excel88\may.xls"

I want to extract the *.xls name in a For Loop.

Thanks & Regards.



"Bob Phillips" wrote:

What do you want, to check that it is there, or its position, or what?

myVar = Instr(1,"Jeans are popular among the young.","popular")

returns its position assuming it exists

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Hello everybody,

Please kindly tell how to extract the key word "popular " by VBA
function,
say Mid, how to:

Dim theString as String
theString = "Jeans are popular among the young."

Thanks & Regards.

Why add the 99 as the length of the MID?


in a worksheetfunction you'll need it, in VBA you dont.
as it can only lead to errors for (exceptionally) long names,
leave it out.

also there's no need to test for instrrev.

TheFile = Mid(TheFullPath, InStrRev(TheFullPath, "\") + 1)

assumes xl2000+/Windows



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

?B?RnJhbmsgS2FiZWw=?= wrote in message
:

Hi
just to add to Bob. Depending on your Excel version (Excel 2000+) you
may also have a look at InStrRev

e.g.
i=instrRev(theString,"/")
myVar = Mid(theString, i + 1, 99)

"Bob Phillips" wrote:

You should work from the back then

Dim myVar As String

For i = Len(theString) To 1 Step -1
If Mid(theString, i, 1) = "\" Then
myVar = Mid(theString, i + 1, 99)
Exit For
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Thanks Bob,

Sorry for my expressions being not clear, say:

"c:\Files\Excel8\peter.xls" "c:\Files\Excel888\michelle.xls"
"c:\Files\Excel88\may.xls"

I want to extract the *.xls name in a For Loop.

Thanks & Regards.



"Bob Phillips" wrote:

What do you want, to check that it is there, or its position, or
what?

myVar = Instr(1,"Jeans are popular among the young.","popular")

returns its position assuming it exists

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Hello everybody,

Please kindly tell how to extract the key word "popular " by
VBA function, say Mid, how to:

Dim theString as String
theString = "Jeans are popular among the young."

Thanks & Regards.

Hi
agree with you on not needing to add '99'. Just copied Bob's formula
:-)
Also not sure what you mean with 'test for instrrev'. If you mean that
you can combine my two line into one - then o.k. of course you could
do this (just a matter of style IMHO). If not: curious about the
meaning of your statement :-)

--
Regards
Frank Kabel
Frankfurt, Germany


keepITcool wrote:
Why add the 99 as the length of the MID?


in a worksheetfunction you'll need it, in VBA you dont.
as it can only lead to errors for (exceptionally) long names,
leave it out.

also there's no need to test for instrrev.

TheFile = Mid(TheFullPath, InStrRev(TheFullPath, "\") + 1)

assumes xl2000+/Windows



www.XLsupport.com | keepITcool chello nl | amsterdam

?B?RnJhbmsgS2FiZWw=?= wrote in message
:

Hi
just to add to Bob. Depending on your Excel version (Excel 2000+)
you
may also have a look at InStrRev

e.g.
i=instrRev(theString,"/")
myVar = Mid(theString, i + 1, 99)

"Bob Phillips" wrote:

You should work from the back then

Dim myVar As String

For i = Len(theString) To 1 Step -1
If Mid(theString, i, 1) = "\" Then
myVar = Mid(theString, i + 1, 99)
Exit For
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Thanks Bob,

Sorry for my expressions being not clear, say:

"c:\Files\Excel8\peter.xls" "c:\Files\Excel888\michelle.xls"
"c:\Files\Excel88\may.xls"

I want to extract the *.xls name in a For Loop.

Thanks & Regards.



"Bob Phillips" wrote:

What do you want, to check that it is there, or its position, or
what?

myVar = Instr(1,"Jeans are popular among the young.","popular")

returns its position assuming it exists

--

HTH

RP
(remove nothere from the email address if mailing direct)


"myBasic" wrote in message
...
Hello everybody,

Please kindly tell how to extract the key word "popular " by
VBA function, say Mid, how to:

Dim theString as String
theString = "Jeans are popular among the young."

Thanks & Regards.

Frank,

no meaning other than I must be tired and misread.
cheerz! Jurgen

Frank Kabel wrote in message
:

If not: curious about the meaning of your statement :-)

Hi Jurgen
know this feeling <vbg

--
Regards
Frank Kabel
Frankfurt, Germany

"keepITcool" schrieb im Newsbeitrag
...
Frank,

no meaning other than I must be tired and misread.
cheerz! Jurgen

Frank Kabel wrote in message
:

If not: curious about the meaning of your statement :-)