Extract Name from File Path

[K]

Hi all, in cell A1 i have file path as below

C:\Documents and Settings\My Documents\Tests\Test1\Simon Brown.txt

and i got macro on a button (see below)

Sub Button1_Click()
cv = Range("a1").Value
Range("a3").Value = Right(cv, Len(cv) - InStrRev(cv, "\"))
End Sub

I get result "Simon Brown.txt" in cell A3 but i just need result
"Simon Brown". So please can anyone help that what changes i should
do in my macro above which should get rid of any thing after dot
like .txt and just give the person name like "Simon Brown"

 

[Per Jessen]

Hi

This should do it:

cv = Range("a1").Value
cv = Right(cv, Len(cv) - InStrRev(cv, "\"))
Range("A3") = Left(cv, InStr(cv, ".") - 1)

Regards,
Per

 

[Rick Rothstein]

That code will work as long as the filename does, in fact, have an
extension. Here is some alternate code which will work whether the filename
has an extension or not...

cv = Range("A1").Value
Range("A3").Value = Split(Split(cv, "\")(UBound(Split(cv, "\"))), ".")(0)