Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:
Solution examples
I need Vlookup help across 2 workbooks into one other main workbook and can't figure out what i'm doing wrong!
Solved by G. E. in 19 mins
I need to create a formula that extracts the prefix from column A
Solved by I. Q. in 42 mins
I am trying to figure out how to use a sumif on SUBSTITUTE(A2,"*","~*") where A2 is a formula with 3371-4
Solved by M. Y. in 19 mins
I have text and number in multiple cells (Ex: Cell 1: 4S, Cell 2: 3.5A, Cell 3: 8O) and want to sum the number values only (Ex: 4 + 3.5 + 8). How can I do this?
Solved by O. A. in 60 mins
i need a formula to combine column y with the number mentioned in column w counting in from 1 to 0 and posting the total count next to number 1 in column v.
Solved by E. J. in 60 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc