To extract the name of a worksheet by formula, you must use 2 functions
- The CELL function
This function returns many properties, like row, column, and file name
- The TEXTAFTER function
This new function will be very useful to extract the right part of the string
The CELL function
The CELL function returns some properties dynamically, such as row number, column number, cell address, ... But there is one that is really interesting, it's the property "filename".
But to work, the workbook must have been saved in your computer. Otherwise, the CELL function with the filename property does not return any data.
But once the file has been saved, the property returns
- the path
- the name of the file, enclosed in square brackets
- and the worksheet name
Keep only the worksheet name
Then, you can extract the worksheet name in two different ways. In the 2 cases, we will base ourselves on the character ] (closed bracket).
You can use the TEXTAFTER function (available only with Excel 365)
Or, using a combination of string length (LEN), and character position (SEARCH)
