< Go Back

Get workbook name and path without sheet

Is it accurate to say that you are hoping to get workbook name and path without making use of the sheet name? You can achieve this by using a formula based on various Excel functions like SUBSTITUTE, LEFT, FIND and CELL functions to return the final result with workbook’s name and path without sheet name, that looks like this:

Path\Workbook_name.xlsx

Formula

Firstly, we start with the generic formula syntax is as follows:

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

Explanation

This formula is a combination of Information and String/Text functions. The following step-by-step explanation will present the formula as below: 

  • The CELL function using the “filename” option returns the name and path of the full workbook and this part of the formula:

CELL(“filename”,A1)

It returns the result as follows:

path\[Workbook_name.xlsx]Sheetname

  • The above full workbook path is then taken into LEFT function as a text string and following FIND function returns the position of closing square bracket “]”, minus 1:

FIND(“]”,CELL(“filename”,A1))-1

The LEFT function returns the following result after extracting the text string:

path\[Workbook_name.xlsx

  • The above text string is then received in SUBSTITUTE function to replace opening square bracket “[“ with a blank string.

This is how it looks like:

=SUBSTITUTE(“path\[workbook_name.xlsx”,"[","")

Example

Secondly, we will go through an example as Figure 1. Suppose we have a workbook’s full name, along with its complete path and sheet name in cell A2. We want to get only workbook’s name and path, without a sheet name. Let’s have a look at following image where we will get workbook name and path without sheet name as shown by using the following formula in the cell:

=SUBSTITUTE(LEFT(CELL("filename",A2),FIND("]",CELL("filename",A2))-1),"[","")

As a result, this formula will return:

path\Workbook_name.xlsx

Figure 1. Get workbook name and path without sheet

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar