Forumla to put filename (with no [ or ]) in a cell

[Claire_Brummell]

Hey,

I've tried the standard method for this.

I want the filname, without path and .xls in the cell.

But there's only one sheet so the full path is

S:\Brand\Third Party channels\Getty\Reports\06-10-17 - October Reports\Hallmark Folder - October Report.xls

As such the formulae using search functions and the "[" "]" as the tags to search for don't work.

I'm sure it's simple, just need help pulling out the right info!

Thanks,

Claire

 

[texasalynn]

take a look at this
http://www.mrexcel.com/board2/viewtopic.php?t=185130

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

 

[Claire_Brummell]

Hi

Thanks for this - however as I said the path doesn't have a "[" or "]" in it, and therefore this doesn't work (this is one of the options I was using earlier)

 

[Greg Truby]

Claire define a named range (actually this will be a named formula) with the name DocName and where refers to is =Get.Document(88)

<s>Then you can just put =DocName in the cell.</s>
^edit oops - you said w/o the extension, so =SUBSTITUTE(DocName,".xls","") _/edit

<hr />
A.Lynn -- I think I liked yer other pic better or do you really look that much like Streisand?

 

[Marc]

a simple formula:

=LEFT(FILENAME(),LEN(FILENAME())-4)

 

[Greg Truby]

a simple formula:

=LEFT(FILENAME(),LEN(FILENAME())-4)
-Marc

I am unfamiliar with any built-in function by that name [filename()]. And, apparently so is my version of Excel (2003), for it responds with #NAME?

 

[Marc]

My Bad on the filename() function. It comes with the MOREFUNC add-in. I just assumed it was an excel function.


Laurent Longre’s MOREFUNC.XLL Excel Add-In
http://www.rhdatasolutions.com/morefunc/

 

[texasalynn]

A.Lynn -- I think I liked yer other pic better or do you really look that much like Streisand?

Ok on the Barbra pic. I even had a more recent one of me but thought it might scare some people, so put up this one instead.

Claire_Brummell - sorry about that some times you read quickly through the post and don't always pick up the entire problem defined.

 

[texasalynn]

so Claire_Brummell does this give you what you need

=MID(CELL("filename",A1),SEARCH(".xls",CELL("filename"))+4,SEARCH(RIGHT(CELL("filename",A1)),CELL("filename",A1)))

 

[Greg Truby]

Audry -- does Cell("filename") return a path+name+sheetname with or without the brackets? For me it returns with brackets, even when the workbook only contains one worksheet. Eg: this is what I get on a test workbook:<ul>[*]C:\Documents and Settings\trubyg\My Documents\[book34.xls]Sheet1[/list]

This works for me:<ul>[*]=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".",CELL("filename"))-FIND("[",CELL("filename"))-1)[/list]