Combining parts of filename and tabname in Excel cell

Try this:

=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+5, SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-14)&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Select all Open in new window

---

»bp

Sure in VBA, add this function, and then use it in a formula like:

=LogNumber()

Public Function LogNumber()
    a = Split(Split(ThisWorkbook.Name, ".")(0), "-")
    LogNumber = a(1) & "-" & a(2) & "-" & ThisWorkbook.ActiveSheet.Name
End Function

Select all Open in new window

---

»bp

It worked okay for me on other sheets.

So, what are the rules on finding the "EE" and "1711" in the file name, being very specific?

---

»bp

You didn't have to change the VBA at all, nor pass anything to it.  Here is a slightly tidier version that makes sure it gets the sheet name of the sheet the formula is on, just to be safe.  I also changed the way it gets the data to assume the parts you want are to the immediate left of the REV part, which is assumed to be last.  So anything else to the far left doesn't matter.  For example these work:

xxxxxxx-yyyyyyy-EE-1711-Rev1.xls
xxxxxxx-yyyyyyy-EE-1711-Rev1b.xls
xxxxxxx-EE-1711-Rev1.xls
EE-1711-Rev1.xls

Public Function LogNumber()
    a = Split(Split(ThisWorkbook.Name, ".")(0), "-")
    LogNumber = a(UBound(a) - 2) & "-" & a(UBound(a) - 2) & "-" & Application.Caller.Worksheet.Name
End Function

Select all Open in new window

---

»bp

You needed that VBA code in a new Module, not attached to a sheet.

And then you needed to add the formula to call it on each sheet in the cell you wanted it to appear in.

See attached.

DTL-EE-1711-Rev4.xlsm

---

»bp

Very welcome Bill.

---

»bp