PDA

View Full Version : Extracting a specific part from a string



avadhutd2
02-24-2010, 07:05 AM
I have one query about extracting a specific part from a string.

Consider example of - D:\My Documents\Test Folder\MyTestFile.xls

In this case I need the file name "MyTestFile.xls" only from the above entire file path.

Since the path may vary every time as -
D:\Test Folder\TestFile1.xls
OR
D:\My Documents\Test Folder1\Test Folder2\Sub Folder1\TestFile2.xls

So it seems difficult to use string function "RIGHT" in this case. What I feel is the only use of extracting the part of the string after last occurence of "\" in the string (i.e catching FILENAME from the path after "\" occurs the last time in the string).

But I am not sure how to achieve it. Can anyone guide me to get that done?

Thanks in advance!!

JKwan
02-24-2010, 07:16 AM
try this:

Sub test()
Const FullPath = "d:\aa\aa\test.xls"

Filename = Mid(FullPath, InStrRev(FullPath, "\") + 1, Len(FullPath) - InStrRev(FullPath, "\") + 1)

End Sub

Bob Phillips
02-24-2010, 07:31 AM
Another way



MsgBox Split(fName, "\")(UBound(Split(fName, "\")))

p45cal
02-24-2010, 07:41 AM
just using worksheet formulae:

=MID(G27,FIND("¬",SUBSTITUTE(G27,"\","¬",LEN(G27)-LEN(SUBSTITUTE(G27,"\",""))))+1,999)where G27 was the cell containing the full path

ZVI
02-24-2010, 07:13 PM
try this:

Sub test()
Const FullPath = "d:\aa\aa\test.xls"

Filename = Mid(FullPath, InStrRev(FullPath, "\") + 1, Len(FullPath) - InStrRev(FullPath, "\") + 1)

End Sub


Shorter a bit: Filename = Mid$(FullPath, InStrRev(FullPath, "\") + 1)

krishhi
02-25-2010, 04:06 AM
Shorter a bit: Filename = Mid$(FullPath, InStrRev(FullPath, "\") + 1)

Great tip mate. :)