Fórmula genérica
=MID(CELL("filename",A1),FIND(")",CELL("filename",A1))+1,255)
Resumo
Para obter o nome da planilha atual (ou seja, guia atual), você pode usar uma fórmula baseada na função CELL. O CELL recupera o nome da pasta de trabalho e a planilha, e as funções MID e FIND são usadas para extrair apenas o nome da planilha. No exemplo mostrado, a fórmula em E5 é:
=MID(CELL("filename",A1),FIND(")",CELL("filename",A1))+1,255)
Explicação
A função CELL é usada para obter o nome completo do arquivo e o caminho:
CELL("filename",A1)
O resultado é o seguinte:
path(workbook.xlsm)sheetname
CELL retorna este resultado para a função MID como o argumento de texto. O nome da folha começa logo após o colchete esquerdo, então a posição inicial é calculada com FIND:
FIND(")",CELL("filename",A1))+1
O número de caracteres a extrair é codificado como 255. Na IU do Excel, você não pode nomear uma planilha com mais de 31 caracteres, mas o formato do arquivo em si permite nomes de planilha de até 255 caracteres, portanto, isso garante que o nome inteiro seja recuperado.
Alternativa com DIREITO
Você também pode usar a função RIGHT para extrair o nome da folha, em vez de MID:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND(")",CELL("filename",A1)))
Isso requer mais chamadas de função, mas funciona tão bem.
