Fill cell with sheet name Microsoft Excel

I have an excel file which has around 100 sheets in it. Each sheet is for a student, and each sheet name is the firstname_lastname e.g Mark_Hope so what I want to do is that in each sheet there are two cells for the firstname and lastname I want to automatically fetch and fill these two cells with the first and last names from the sheet name. Any advice please?

This is what I tried but for some reason it's not working properly, plus it updates all the sheets not just the current one.

For the lastname:

=RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,30),FIND("_",MID(CELL("filename"),FIND("]",CELL("filename"))+1,30))-1)

For the firstname:

=LEFT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,30),FIND("_",MID(CELL("filename"),FIND("]",CELL("filename"))+1,30))-1)

Answer

For first name

=LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256), FIND("_", MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256))-1)

for Last name

=MID(CELL("filename",A1),FIND("_",CELL("filename",A1))+1,256)

Not sure what you mean by "it updates all the sheets" - these are formulas that you should paste in the cells on each sheet.

Also as a hint, using the Evaluate Formula on the Formulas ribbon steps through each part of the formula and shows you where it is going wrong.

You could also simplify these formulas by getting the sheet name into one cell then using that cell in the other two formulas.

Using a VBA solution means remembering to rerun the script when you change the sheet name.