Extracting Workbook name

B

brian

I have a workbook which has a list of path and filenames for workbooks.
I want to extract from each of these the workbook name only.
e.g From this path I want to extract only the name "Lucky Draw.xls"

C:\Documents and Settings\Bbrian\My Documents\My Documents\Brian
docs\Clay\Clay WorkBooks\Workbooks\Lucky Draw.xls

Each of the path names are vaiable lengths and a variable number of sub
direcotries
 
R

Ron Rosenfeld

I have a workbook which has a list of path and filenames for workbooks.
I want to extract from each of these the workbook name only.
e.g From this path I want to extract only the name "Lucky Draw.xls"

C:\Documents and Settings\Bbrian\My Documents\My Documents\Brian
docs\Clay\Clay WorkBooks\Workbooks\Lucky Draw.xls

Each of the path names are vaiable lengths and a variable number of sub
direcotries
Click to expand...

This will return the string that begins after the final "\" :

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255)


--ron
 
C

calummurdo

I have a workbook which has a list of path and filenames for workbooks.
I want to extract from each of these the workbook name only.
e.g From this path I want to extract only the name "Lucky Draw.xls"

C:\Documents and Settings\Bbrian\My Documents\My Documents\Brian
docs\Clay\Clay WorkBooks\Workbooks\Lucky Draw.xls

Each of the path names are vaiable lengths and a variable number of sub
direcotries
Click to expand...

Hi Brian,
You can create a user defined function to achieve this. I found this
one here:
http://www.ozgrid.com/VBA/GetExcelFileNameFromPath.htm

Function FunctionGetFileName(FullPath As String)
Dim iCount As Integer
Dim StrFind As String
Do Until Left(StrFind, 1) = "\"
iCount = iCount + 1
StrFind = Right(FullPath, iCount)
If iCount = Len(FullPath) Then Exit Do
Loop
FunctionGetFileName = Right(StrFind, Len(StrFind) - 1)
End Function

Best Regards,

CalumMurdo Kennedy
 
B

Bernard Liengme

Here is a User Defined Function that does it
Function FindName(mytext)
x = Len(mytext)
temp = ""
For j = x To 1 Step -1
myletter = Mid(mytext, j, 1)
If myletter <> "\" Then
temp = myletter & temp
Else
Exit For
End If
Next j
FindName = temp
End Function

Need help with VBA? See David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
 
B

brian

Ron said:
This will return the string that begins after the final "\" :

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255)


--ron
Click to expand...
 
B

brian

Ron Rosenfeld seems to have offered the solution to my problem. Thanks
to all who replied
 
R

Ron Rosenfeld

Ron Rosenfeld seems to have offered the solution to my problem. Thanks
to all who replied
Click to expand...

You're welcome. Glad to help. Thanks for the feedback.
--ron
 
Top