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

Claire_Brummell

Board Regular
Joined
Sep 29, 2004
Messages
129
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Claire_Brummell

Board Regular
Joined
Sep 29, 2004
Messages
129
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
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>
<sup>edit</sup> oops - you said w/o the extension, so =SUBSTITUTE(DocName,".xls","") <sub>/edit</sub>

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

Marc

Active Member
Joined
Feb 21, 2002
Messages
388

ADVERTISEMENT

a simple formula:

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

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
a simple formula:

=LEFT(FILENAME(),LEN(FILENAME())-4)
-Marc
:unsure: 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? :unsure:
 

Marc

Active Member
Joined
Feb 21, 2002
Messages
388

ADVERTISEMENT

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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
so Claire_Brummell does this give you what you need
Code:
=MID(CELL("filename",A1),SEARCH(".xls",CELL("filename"))+4,SEARCH(RIGHT(CELL("filename",A1)),CELL("filename",A1)))
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
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]
 

Forum statistics

Threads
1,141,630
Messages
5,707,520
Members
421,512
Latest member
jc364698

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top