Automated File Names

This article derives a formula to display the name of the workbook automatically. But be warned: it ain’t pretty. By Liam Bastick, Director with SumProduct Pty Ltd.

Query

For version control purposes, I want to display the current filename of my Workbook within one or more worksheets. Is there a way to do this short of typing it in manually?

Advice

Where there’s a will there’s a way, and I use the following formula:

=IF(ISERROR(OR(FIND(“[",CELL("filename",A1)),FIND("]“,CELL(“filename”,A1)))),”",MID(CELL(“filename”,A1),
FIND([",CELL("filename",A1))+1,FIND("]“,CELL(“filename”,A1))-FIND(“[",CELL("filename",A1))-1))

Obvious, yes?

Professional modellers often have a “rule of thumb” when writing a formula. Literally. In general, for formulae to be transparent, they should appear no longer than the length of your thumb in the formula bar. Of course, there are exceptions to many great rules, and alas, this is one of them.

Therefore, I think the above formula needs explanation.

Padded CELL

CELL(Info_Type,[Reference]) returns information about the formatting, location, or contents of the upper-left cell in a reference (in our example, we will be using cell A1 as our reference in the active worksheet, but this selection is entirely arbitrary).

Info_Type returns various information depeding upon what has been selected:

“address” – Reference of the first cell in reference, as text;
“col” – Column number of the cell in reference;
“color” – 1 if the cell is formatted in colour for negative values; otherwise returns 0 (zero);
“contents” – Value of the upper-left cell in reference; not a formula;
“filename” – Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved;
“format” – Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in colour for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values;
“parentheses” – 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0;
“prefix” – Text value corresponding to the “label prefix” of the cell. Returns single quotation mark (‘) if the cell contains left-aligned text, double quotation mark (“) if the cell contains right-aligned text, caret (^) if the cell contains centred text, backslash (\) if the cell contains fill-aligned text, and empty text (“”) if the cell contains anything else;
“protect” – 0 if the cell is not locked, and 1 if the cell is locked;
“row” – Row number of the cell in reference;
“type” – Text value corresponding to the type of data in the cell. Returns “b” for blank if the cell is empty, “l” for label if the cell contains a text constant, and “v” for value if the cell contains anything else; and
“width” – Column width of the cell rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.

We therefore use the syntax =CELL(“filename”,A1). An example of a returned filename might be:

C:\Documents and Settings\Liam\My Documents\Spreadsheet Doctor\Doctor 30 – Automated Filename\[Example Workbook.xls]Sheet1

This is not what is required, there’s ‘padding’. All we want is the actual filename, in this case ‘Example Workbook.xls’. Therefore, we need to extract the filename from this worksheet directory path.

This will be a three-step process.

Step 1: FINDing the Beginning and the End

The directory path will vary for each file, so we need to spot a foolproof method of finding the beginning and the end of the workbook name. Fortunately, Excel assists us here. ‘[’ and ‘]’ are reserved characters in Excel’s syntax and denote the beginning and the end of the workbook name.

The example returned filename above is 122 characters long. If we can find the position of the ‘[’ and ‘]’ we will be on our way.

FIND(find_text,within_text,start_num) is the function we need, where:

find_text is the text you want to find;
within_text is the text containing the text you want to find; and
start_num (which is optional) specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

So, in our example, =FIND(“[",CELL("filename",A1)) returns the value 95 and the formula =FIND("]“,CELL(“filename”,A1)) returns the value 116. In other words, for our illustration, if we can get Excel to return the character string in positions 96 to 115 inclusive (i.e. between the square brackets) we will have our workbook name.

Step 2: LEFT a bit, RIGHT a bit, Aim for the MID Section

There are various functions in Excel that will return part of a character string:

LEFT(text,num_characters) returns the first few characters of a string depending upon the number specified (num_characters). This is not useful here as we do not want the first few characters of our text string;
RIGHT(text,num_characters) returns the last few characters of a string depending upon the number specified (num_characters). This is not useful here either as we do not want the last few characters of our text string; and
MID(text,start_num,num_characters) returns a specific number of characters from a text string, starting at the position specified, based on the number of characters chosen.

Therefore, we should use the MID function here. In hard code form, our formula would be:

=MID(CELL(“filename”,A1),96,20)

where:

96 = position one character to the right of ‘[’ (95 + 1); and
20 which is the length of the filename string, being the position of ‘]’ less the position of ‘[’ less 1, i.e. 116 – 95 – 1 = 20.

This gives us our filename ‘Example Workbook.xls’.

The problem is we don’t want hard code: a flexible formula is required. Using the concepts explained above, we derive:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]“,CELL(“filename”,A1))-FIND(“[",CELL("filename",A1))-1)

And so we are done. Except we aren’t.

Step 3: Error Trapping

A good modeller will always ensure that a formula will work in all foreseeable circumstances. The above formula will only work if the file has been named and saved. Otherwise, CELL("filename",A1) will return empty text (“”), which will cause the embedded FIND formulae to return #VALUE! errors, and hence the overall formula will also return the #VALUE! error.

We therefore need an error trap, i.e. a check that ensures if the file has not yet been saved we just get empty text (“”) returned. To do this, we can use the following formula:

=IF(ISERROR(OR(FIND("[",CELL("filename",A1)),FIND("]“,CELL(“filename”,A1)))),”",1)

ISERROR(expression) gives a value of TRUE if the expression is evaluated as an error, otherwise it is FALSE. In our equation above, if the file has not been saved, this formula will return the empty text (“”), otherwise it will return our ‘dummy’ value of 1. Substituting our derived formula above for the 1 gives us the final formula:

=IF(ISERROR(OR(FIND(“[",CELL("filename",A1)),FIND("]“,CELL(“filename”,A1)))),”",MID(CELL(“filename”,A1),
FIND(“[",CELL("filename",A1))+1,FIND("]“,CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1))

It isn’t pretty, it’s not short, it’s not transparent, but it’s flexible and robust.

Final Note

The formula above is intended to be copied – as is – straight into an Excel worksheet by pasting it directly into the Excel formula bar and pressing ENTER. In certain situations, it will not work due to the exact method of copying employed, fonts used or the set-up of the ASCII characters.

In this instance, try re-typing all of the inverted commas (“ and ”) in the formulae first. If this doesn’t work, I apologise, but you will have to re-type it. C’est la vie.