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

On 1 Oct 2006 10:31:11 -0700, wrote:

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

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

Ron Rosenfeld wrote:
On 1 Oct 2006 10:31:11 -0700, wrote:

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

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

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

On 2 Oct 2006 07:58:58 -0700, wrote:


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

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

wrote:
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

Hi Brian,
You can create a user defined function to achieve this. I found this
one he
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

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
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