Extract keyword from a string

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

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)

 

[keepITcool]

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

 

[Frank Kabel]

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

 

[keepITcool]

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

 

[Frank Kabel]

Hi Jurgen
know this feeling <vbg>