flie path at a lower directory

[cmendes]

Dear all,

I want a formula that gives me the path of the file but without a last folder. I know the formula for the complete path: =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)

For example, the complete file path is:
C:\Documents and Settings\Cesar\MyDocuments\fTIR\wt_RAL-315\1_28_11Ral_WT_1\Results\

I'm looking for a formula that gives me:

C:\Documents and Settings\Cesar\MyDocuments\fTIR\wt_RAL-315\1_28_11Ral_WT_1\

i.e. without 'Results\'

Many thanks!!
Cesar
<table style="width: 471px; height: 38px;" border="0" cellpadding="0" cellspacing="0"><col width="325"><tr height="17"> <td class="xl65" style="height: 12.75pt; width: 244pt;" width="325" height="17">
</td> </tr></table>

 

[Joe4]

I'm sure that there is probably a way to do it with native Excel function, and even a better way to do it using a User Defined Function better than the one that I am using (reversing the string to find the second instance of the slash), but this User Defined Function seems to work:

Function FilePathOneBack(myFileName) As String
 
    Dim i As Long
    Dim myLen As Long
    Dim myCount As Byte
    
'   Get length of file name
    myLen = Len(myFileName)
    
'   If len of file name is greater than 1 then proceed
    If myLen > 1 Then
'   Loop through entry backwards, looking for backslashes
        For i = myLen To 1 Step -1
'   If backslash found count it
            If Mid(myFileName, i, 1) = "\" Then
                myCount = myCount + 1
'   When you find second backalsh, stop and capture value up to that point
                If myCount = 2 Then
                    FilePathOneBack = Left(myFileName, i)
                    Exit For
                End If
            End If
        Next i
    End If
    
'   If cannot find two backslashes, return "INAVLID"
    If Len(FilePathOneBack) = 0 Then
        FilePathOneBack = "INVALID"
    End If
    
End Function

So, to you it on your spreadsheet, you would just do this:
=FilePathOneBack(CELL("filename"))

 

[T. Valko]

Dear all,

I want a formula that gives me the path of the file but without a last folder. I know the formula for the complete path: =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)

For example, the complete file path is:
C:\Documents and Settings\Cesar\MyDocuments\fTIR\wt_RAL-315\1_28_11Ral_WT_1\Results\

I'm looking for a formula that gives me:

C:\Documents and Settings\Cesar\MyDocuments\fTIR\wt_RAL-315\1_28_11Ral_WT_1\

i.e. without 'Results\'

Many thanks!!
Cesar
<TABLE style="WIDTH: 471px; HEIGHT: 38px" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL width=325><TBODY><TR height=17><TD class=xl65 style="WIDTH: 244pt; HEIGHT: 12.75pt" width=325 height=17>


</TD></TR></TBODY></TABLE>

Try this...

Create this named formula...

Name: FileName
Refers to: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

Then, to get the result you want:

=LEFT(FileName,FIND("^^",SUBSTITUTE(FileName,"\","^^",(LEN(FileName)-LEN(SUBSTITUTE(FileName,"\","")))-1)))

Note that the file must have been saved and given a name.

Also assumes that there will be at least 2 "\" in the path.

 

[cmendes]

Try this...

Create this named formula...

Name: FileName
Refers to: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

Then, to get the result you want:

=LEFT(FileName,FIND("^^",SUBSTITUTE(FileName,"\","^^",(LEN(FileName)-LEN(SUBSTITUTE(FileName,"\","")))-1)))

Note that the file must have been saved and given a name.

Also assumes that there will be at least 2 "\" in the path.

I tried your suggestion but it did not work, maybe i'm not understanding.
I placed the second formula on a cell and i got a error (#Name?). Is there any other formula that i have to include in another cell?
Thanks
C

 

[T. Valko]

I tried your suggestion but it did not work, maybe i'm not understanding.
I placed the second formula on a cell and i got a error (#Name?). Is there any other formula that i have to include in another cell?
Thanks
C

What version of Excel are you using?

 

[cmendes]

What version of Excel are you using?

2007 XP...I can check on other computers

 

[T. Valko]

2007 XP...I can check on other computers

OK, you need to create a defined named formula and the menu locations to accomplish that is different for different versions of Excel.



If you're using Excel 2007 or later...

• Goto the Formulas tab>Defined Names>Define Name
• Name: FileName
• Refers to: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
• OK out

If you're using Excel 2003 or earlier...

• Goto the menu Insert>Name>Define
• Name: FileName
• Refers to: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
• OK out

Then, in some cell on the worksheet enter this formula:

=LEFT(FileName,FIND("^^",SUBSTITUTE(FileName,"\","^^",(LEN(FileName)-LEN(SUBSTITUTE(FileName,"\","")))-1)))

Note that this will only work if the file has been saved and given a name.

 

[cmendes]

OK, you need to create a defined named formula and the menu locations to accomplish that is different for different versions of Excel.



If you're using Excel 2007 or later...

• Goto the Formulas tab>Defined Names>Define Name
• Name: FileName
• Refers to: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
• OK out

If you're using Excel 2003 or earlier...

• Goto the menu Insert>Name>Define
• Name: FileName
• Refers to: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
• OK out

Then, in some cell on the worksheet enter this formula:

=LEFT(FileName,FIND("^^",SUBSTITUTE(FileName,"\","^^",(LEN(FileName)-LEN(SUBSTITUTE(FileName,"\","")))-1)))

Note that this will only work if the file has been saved and given a name.

Yes!!!! Thank you so much!!
Best,
C

 

[T. Valko]

Yes!!!! Thank you so much!!
Best,
C

You're welcome. Thanks for the feedback!

 

[cmendes]

OK, you need to create a defined named formula and the menu locations to accomplish that is different for different versions of Excel.



If you're using Excel 2007 or later...

• Goto the Formulas tab>Defined Names>Define Name
• Name: FileName
• Refers to: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
• OK out

If you're using Excel 2003 or earlier...

• Goto the menu Insert>Name>Define
• Name: FileName
• Refers to: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
• OK out

Then, in some cell on the worksheet enter this formula:

=LEFT(FileName,FIND("^^",SUBSTITUTE(FileName,"\","^^",(LEN(FileName)-LEN(SUBSTITUTE(FileName,"\","")))-1)))

Note that this will only work if the file has been saved and given a name.

By the way, does this hold true to all the excel files that I use in the future? I tried in another saved file and it did not work, I had to go to Defined Names>Define Name.
Thanks
C