Go Back

How to Get a Workbook Name and Path without Sheet in Excel

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.

Get a workbook name and path without sheet name in Excel

With the combined functions mentioned above, you can find the 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 the 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

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'm trying to get the name of closed workbooks in a given file path as list in an excel file.
Solved by B. H. in 12 mins
Pls refer to the attached file. The macros code is on sheet 2. The formulas used in the attached excel uses a different workbook therefore a defined path to that excel workbook is hardcoded I want the hardcoded path to be replaced by a code that prompts user to choose a file from the browser
Solved by E. W. in 23 mins
Hi, I have to collect data from different workbook and consolidate in new workbook. Each workbook has the common identifier to identify the rows also each sheet i need only specific fields. How to select specific fields from each workbook and place it in new worksheet. Pls guide. Eg: First sheet has id and name, second sheet has id, name , owner , description third sheet has name,business owner, severity, Fourth sheet Contact number.name I want to collect all the information and have it in the new workbook
Solved by O. J. in 26 mins

Leave a Comment

avatar