Formula to return the name of the worksheet

[Tigger12b]

I have managed to find the formula to return the name of current filename and current worksheet in a cell in the worksheet

=RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))

However I want to get just the name of the worksheet, but am having some difficulty determining what the formula should be to get that, can anyone help?

[#2]

Hi @Tigger12b 

Excel 2021/365

=LET(
    path, CELL("filename"),
    p,    FIND("]",path),
    MID(path,p+1,LEN(path)-p)
)

Other versions

=MID(
    CELL("filename"),
    FIND("]",CELL("filename"))+1,
    LEN(CELL("filename"))-FIND("]",CELL("filename"))
)

[#3 · Accepted solution]

@Tigger12b 

One caveat - if you don't include a cell reference for the CELL function, then the formula will return the name of the *active* worksheet when excel calculates or re-calculates the formula. So, if you enter the formula on Sheet2, it will return "Sheet2", but then if you go to Sheet1 and excel re-calculates, then your formula on Sheet2 will now say "Sheet1".

I would use the same cell in which I entered the formula, so if you entered this in cell D2:
=MID(CELL("filename",D2), FIND("]", CELL("filename",D2))+1,255)

Personally, I like to set up a local named formula in name manager called TabName, using:
=MID(CELL("filename",INDIRECT("A1")), FIND("]", CELL("filename",INDIRECT("A1")))+1,255)

Of course, if you have the let function, you could eliminate the redundant part:

=Let(fname, CELL("filename",INDIRECT("A1")), MID(fname, FIND("]", fname)+1,255))