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.