Extract Worksheet Names Only
Now we have found out what the file and path of our worksheet is we can extract the actual worksheet name.
In B2, enter the following formula:
="'"&MID(A2,FIND("]",A2)+1,256)&"'!",
In the previous example, this formula returns ''Sheet1''!.
Note the use of the two single apostrophes in the result. This allows for worksheet names that have spaces in their name. It's unnecessary for worksheet names without spaces, but it doesn't do any harm to cover your bases-that is, should you change the worksheet name to include a space.
Copy the previous formula down to reference all the data in Column A. In B1, enter the heading Worksheet Names. Highlight/select B1 down until the last formula rowin Column B. Now, select Formulas → Defined Names → Create from Selection (pre-2007, Insert → Name → Create). Ensure only Top Rowis checked and click OK. Excel will create the named range Worksheet_Names, omitting cell B1.
