Excel formula to Extract the Worksheet Name

Excel formula to Extract the Worksheet Name
Last Updated on 31/03/2024
Reading time: 2 minutes

To extract the name of a worksheet by formula, you must use 2 functions

  1. The CELL function

    This function returns many properties, like row, column, and file name

  2. 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".

CELL function with 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.

CELL function returns no result

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
Result of the CELL function

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)

Extract of the worksheet name with the TEXTAFTER function

Or, using a combination of string length (LEN), and character position (SEARCH)

Extract of the worksheet name with the RIGTH LEN SEARCH functions

Related article

Leave a Reply

Your email address will not be published. Required fields are marked *

Excel formula to Extract the Worksheet Name

Reading time: 2 minutes
Last Updated on 31/03/2024

To extract the name of a worksheet by formula, you must use 2 functions

  1. The CELL function

    This function returns many properties, like row, column, and file name

  2. 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".

CELL function with 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.

CELL function returns no result

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
Result of the CELL function

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)

Extract of the worksheet name with the TEXTAFTER function

Or, using a combination of string length (LEN), and character position (SEARCH)

Extract of the worksheet name with the RIGTH LEN SEARCH functions

Related article

Leave a Reply

Your email address will not be published. Required fields are marked *