How the heck?

[Dave]

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

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

Many thanks - Dave.

 

[Barb Reinhardt]

Save your file and you'll see the result.

 

[Gord Dibben]

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

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


Gord Dibben MS Excel MVP

 

[Dave]

Hi Gordon,
Thanks for the link.
Dave.

 

[BoniM]

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]

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

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?

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

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

Many thanks - Dave.

 

[BoniM]

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

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

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?

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

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

Many thanks - Dave.