Automating in Excel: a worksheet display tip

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

The quick answer is 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?

At my company, we 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 some explanation.

Padded CELL

The formula is built using the CELL function, which returns information about the formatting, location, or contents of a cell. The CELL function has the following syntax:

CELL(info_type, [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 is required and is a text value that specifies what type of cell information the function should return. Here is a list of possible Info_Type values and the corresponding results.

"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 a 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.

"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 (ie, between the square brackets) we will have our workbook name.

Step 2: LEFT a bit, RIGHT a bit, aim for the MID section

For the next step, we turn to the following Excel functions that 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.
• 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, ie, 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 work only 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 a #VALUE! error.

We therefore need an error trap, ie, 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.

Word to the wise

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 setup of the ASCII characters.

In this instance, try retyping all of the inverted commas/quotation marks (“ and ”) in the formula first. If this doesn’t work, I apologise, but you will have to retype it. C’est la vie.

— Liam Bastick is director of SumProduct, a global consultancy specialising in Excel training. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.