We help IT Professionals succeed at work.

Combining parts of filename and tabname in Excel cell

178 Views
Last Modified: 2017-10-27
I have an Excel worksheet (attached) that I would like cell A30 to contain part of the filename and the entire tabname.

For example, the attached filename is DTL-EE-1711-Rev3.

I have successfully gotten EE-1711- to print in A30. Now I would like to add the Tabname to the cell so that the cell prints

EE-1711-02 in A30

For internal security reasons, I would prefer this is done with VBA instead of an Excel formula.

Obviously, by the end of the month, there will be Tabs 01, 02, 03, 04 ... through 30 or 31 skipping Saturday and Sunday.
DTL-EE-1711-Rev3.xlsm
Comment
Watch Question

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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)

Open in new window


»bp
Bill GoldenExecutive Managing Member

Author

Commented:
Worked perfectly. Can we accomplish the same thing in VBA?

More questions coming regarding of features with the same spreadsheet.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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

Open in new window


»bp
Bill GoldenExecutive Managing Member

Author

Commented:
Well that works for Tab 02 but does not work when you duplicate Tabs 03 and 04.

In addition while the worksheet is named

EE-1711-Rev2, occasionally it could be Rev2A. You can count on the YYMM appearing between the two dashes and that those will be the only two dashes.  We can force a 3-character lead like EEE-1711-RevX so that you could count on position 5 to be YY and position 7 to be MM.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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
Bill GoldenExecutive Managing Member

Author

Commented:
I misspoke earlier. It is not working in Tab 02 (I forgot to remove the original formula from the cell. I think, but am not sure, that I was supposed to insert the cell number in the VBA code, but did see where to do so and put it in "This worksheet" so that it appears in every tab.

Am I confused?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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

Open in new window


»bp
Bill GoldenExecutive Managing Member

Author

Commented:
Well, I obviously am doing something in error adding the VBA code. I have attached a copy of the original spreadsheet with Tabs 02, 03 and 04 with the code added. None are working.
DTL-EE-1711-Rev4.xlsm
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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
Bill GoldenExecutive Managing Member

Author

Commented:
Now I am getting something. But instead of EE-1711-01, I am getting EE-EE-01. Something I did?
Bill GoldenExecutive Managing Member

Author

Commented:
By the way, using the same procedure, the TabDate routine (from the other question) works perfectly.  

Bill
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Bill GoldenExecutive Managing Member

Author

Commented:
Fabulous. That did the trick. Saves hours. Thanks again, more questions to come, Bill
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Very welcome Bill.


»bp

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions