Forum Discussion
update Excel File Name (Rename an Excel Sheet), and it auto updates a specific cell in the workbook
Budman36 It sounds like you want the filename in a cell. The function CELL() offers that sort of. CELL("filename") actually gives the full path/reference but with some additional work you can have it trim just the filename out:
=LET(path,CELL("filename"),start,SEARCH("[",path)+1,end,SEARCH("]",path),fn,MID(path,start,end-start),fn)if you don't have the newest version of Excel with the LET() function you can use:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)but maybe the full path would be even better for you?
mtarler This worked very well, on a local drive, personal computers, etc., but it doesn't work when uploaded to sharepoint? Any ideas?
CELL("filename") is not implemented for Excel Online
SergeiBaklan That would do it! Do you know a workaround?
mtarler The MID works, but I am hitting a road block.
Here is my example of a book name:
21MJNXXX. Customer Name. Job Name. REV 1
I need to separate each section into a cell of there own...
21MJNXXX
Customer Name
Job Name
REV 1
The "Text to Columns" function will not work because it shows the formula. Not the actual text. I have tried =LEFT, =LEFT(RIGHT, .... =LEFT(MID... but I can't seem to get this right? Is it possible do the text to columns with code?
As variant
=LET(txt, A1, delim, ".", nDelim, LEN(txt)-LEN(SUBSTITUTE(txt, delim,"")), ch, UNICHAR(9999), n, SEQUENCE(1,nDelim+1), nn, SEQUENCE(1,nDelim+1,2), startPos,IFERROR(FIND(ch,SUBSTITUTE(txt,delim,ch,n-1))+1,1), numChars, IFERROR(INDEX(startPos,nn)-1,LEN(txt)+1)-startPos, TRIM(MID(txt,startPos,numChars)) )for
SergeiBaklan This worked as well. thank you both!
Budman36 Yes you can use similar techniques to further divide the filename. I assume a period (.) is between each part so do you want the full name in 1 cell and then break it down or only have the parts?
for example if the =MID() formula is in cell A1 then you could paste this in A2 and copy down:
=TRIM(MID(SUBSTITUTE($A$1,".",REPT(" ",LEN($A$1))),LEN($A$1)*(ROWS($A$1:A1)-1)+1,LEN($A$1)))
