How the heck?
 

Could someone please explain to me how the following function works?

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Many thanks - Dave.

How the heck?
 

Save your file and you'll see the result.
--
HTH,
Barb Reinhardt



"Dave" wrote:

Could someone please explain to me how the following function works?

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Many thanks - Dave.

How the heck?
 

See Bob Phillips' site for the "how" part.

http://www.xldynamic.com/source/xld.xlFAQ0002.html


Gord Dibben MS Excel MVP

On Mon, 7 Apr 2008 14:56:19 -0700, Dave wrote:

Could someone please explain to me how the following function works?

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Many thanks - Dave.

How the heck?
 

Hi Gordon,
Thanks for the link.
Dave.

How the heck?
 

The Cell function returns information about the given cell, if the info type
(first argument) of filename is requested, the returned info is the complete
path to that cell - for example:
C:\Users\UserName\Documents\[sample.xlsx]Sheet1

The Find function is looking for the first instance of a right square
bracket in the filename info, which indicates the end of the workbook name,
and returns its character position

The Mid function is therefore taking the location of the ] in the path to
the current cell, and starting in the next position, returning up to the next
255 characters, which will be your sheetname.

Does that help?


"Dave" wrote:

Could someone please explain to me how the following function works?

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Many thanks - Dave.

How the heck?
 

Yes, thank-you. I like to understand what I use. Thanks for taking the effort.
Dave.

"BoniM" wrote:

The Cell function returns information about the given cell, if the info type
(first argument) of filename is requested, the returned info is the complete
path to that cell - for example:
C:\Users\UserName\Documents\[sample.xlsx]Sheet1

The Find function is looking for the first instance of a right square
bracket in the filename info, which indicates the end of the workbook name,
and returns its character position

The Mid function is therefore taking the location of the ] in the path to
the current cell, and starting in the next position, returning up to the next
255 characters, which will be your sheetname.

Does that help?


"Dave" wrote:

Could someone please explain to me how the following function works?

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Many thanks - Dave.

How the heck?
 

You're very welcome... it's refreshing to hear from people who want to
understand instead of just wanting it done!

"Dave" wrote:

Yes, thank-you. I like to understand what I use. Thanks for taking the effort.
Dave.

"BoniM" wrote:

The Cell function returns information about the given cell, if the info type
(first argument) of filename is requested, the returned info is the complete
path to that cell - for example:
C:\Users\UserName\Documents\[sample.xlsx]Sheet1

The Find function is looking for the first instance of a right square
bracket in the filename info, which indicates the end of the workbook name,
and returns its character position

The Mid function is therefore taking the location of the ] in the path to
the current cell, and starting in the next position, returning up to the next
255 characters, which will be your sheetname.

Does that help?


"Dave" wrote:

Could someone please explain to me how the following function works?

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Many thanks - Dave.