Cell(filename) question

[braindiesel]

I am using this formula
=CELL("filename")
which produces this result...
C:\Users\Glen Munro\Dropbox\_VBA\___ MY BIG TEST\[to delete duplicate.xlsm]Sheet1
I would like to produce this result
C:\Users\Glen Munro\Dropbox\_VBA\___ MY BIG TEST\to delete duplicate.xlsm

I am solving this with a couple replaces... but is there an auto solution for this?

 

[Joe4]

I do not think there is any clean-cut simple formula to use.
Can you post your "workaround" formula?

 

[braindiesel]

I do not think there is any clean-cut simple formula to use.
Can you post your "workaround" formula?

No workaround formula, just some recorded vba

Selection.Replace What:="]*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="[", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Joe4]

Here is a formulaic solution using no VBA:

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")

 

[braindiesel]

That is much slicker... thanks

 

[Joe4]

You are welcome!