Formula to extract file name not working correctly

[sparky2205]

Hi folks,
I have the following formula in a template:

=MID(@CELL("filename"),SEARCH("[",@CELL("filename"))+1, SEARCH("(",@CELL("filename"))-SEARCH("[",@CELL("filename"))-2)

Each time the template is used it is given a sequential number by the system.
I download two of these documents to a network folder, which are now called, let's say, File1 and File2.
I open the two documents in the order File1, File2.
Now they both have the document number File2 in the field where the formula is.
If I open them in the order File2, File1 they both have the number File1 where the formula is.
i.e. whichever document is opened last both documents will take that document number.
It doesn't matter how many documents you have open all documents will take the number of the last document opened.
I feel like I'm missing something simple but I don't know what.

 

[Anthony47]

Hello @Dave Patton
my contribution was with Cell syntax to avoid the behaviour that @sparky2205 described.
I didn't test Sparky's formula, nor yours; if the same formula yelds different results I guess you (Dave and Sparky) use it in different conditions

 

[Fluff]

The formula that the OP posted will work if you have a ( as part of the filename.

 

[Dave Patton]

Thanks Fluff. I do not use "(" or ")" in file names.

I was just trying to help. It is frustrating when what is intended to be helpful is just ignored.
It is good to know that the post relates a just certain file names and not a generic solution.

 

[Fluff]

I do not use "(" or ")" in file names.

That makes two of us & I was also getting an error until I noticed the ( in one of the Search functions.

 

[sparky2205]

Dave,
my apologies, it was not my intention to ignore you. I did respond to your post but responded to Anthony in error. See post #6.
I don't use brackets in file names myself but this file is being produced from a system so I have to work with what I have.
Again, my apologies, I can understand your frustration when all you're doing is trying to help.

 

[Dave Patton]

sparky2005
Thanks for the comment.
Two questions:
- did the suggestion work
- do the suggestions work in your template or do they have to be made volatile

 

[sparky2205]

As Anthony's solution worked for me I didn't take it any further.

Your formula returns the #NAME error. It appears not to like FileN in the LET function.
I know what a volatile function is but I don't know how to, "make a formula volatile".