File name does not recalculate

[ozbeachbum]

=(LET(f,CELL("filename",A29),REPLACE(REPLACE(f,FIND("]",f),500,""),1,FIND("[",f),"")))
I am using the above formula to show the file name of a template, it works fine until I want to change the name.
When I save as to the same folder it retains the original file name, F9 does not correct it, however when I close and reopen it changes to the correct name.
As it is a template I need to save or move it to a different folder, in both cases when the file is opened all it only shows e-, neither character being part of the file name.
Example of file name being saved: ZZZ 410701 (ph) stf^out^
Any assistance or comments greatly appreciated.

 

[Dave Patton]

I do not know if these functions are in Excel 2021.
I will check for an earlier version.

Try =TEXTBEFORE(TEXTAFTER(CELL("filename",$A$1),"["),".")

 

[ozbeachbum]

I do not know if these functions are in Excel 2021.
I will check for an earlier version.

Try =TEXTBEFORE(TEXTAFTER(CELL("filename",$A$1),"["),".")

Thanks Dave,
Unfortunately it returns NAME?

 

[ozbeachbum]

It looks like it has something to do with the file path being long.
I tried saving and moving it to a shorter file path and it retained the correct name.
Possibly the only solutions are either shorter file path or delete the idea.

 

[Dave Patton]

Test2024.xlsx
	A
10	Test2024
11	Test2024.xlsx
Sheet1

Cell Formulas
Range	Formula
A10	A10	=LET(a,CELL("filename",A2),MID(a,SEARCH("[",a)+1,SEARCH(".",a)-1-SEARCH("[",a)))
A11	A11	=LET(a,CELL("filename",A2),MID(a,FIND("[",a)+1,FIND("]",a)-FIND("[",a)-1))

 

[ozbeachbum]

Both work when saved to same folder and moved to short file path folder, however when I move it to the long file path folder it does not work.