0

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)
2

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.

| improve this answer | |
1

You can do it easily with VBA:

Sub namestocells()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    totalwks = wkb.Worksheets.Count
    For i = 1 To totalwks
        Set wks = wkb.Sheets(i)
        sheetname = wks.Name
        splitname = Split(sheetname, "_")
        mn = UBound(splitname)
        If mn = 1 Then
            wks.Cells(1, 1) = splitname(0)
            wks.Cells(1, 2) = splitname(1)
        End If
    Next i
    a = MsgBox("The process has ended succesfully", vbInformation)
End Sub

It will go through all your worksheets, take the name, split it and put on the cells A1 (wks.Cells(1, 1)) and B1 (wks.Cells(1, 2)) ther first and last names according to the name of the worksheet.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.