How to Get the File Name in A Cell

5

5thQuadrant

New member
Joined
Mar 18, 2021
Messages
20
Reaction score
0
Points
0
Excel Version(s)
MS 365 for Enterprise
Hi ALL. :smile:
Here is my 1st post and my question is...

For example, I have a file "Book1.xlsx"
How to get the 2nd - 4th characters (i.e. "ook" in this case) in the file name and show them in a cell?
Your help is much appreciated.
 
You could use this formula where the last two numbers are First character and Number of characters to pickup:

=MID(TRIM(LEFT(SUBSTITUTE(MID(CELL("filename"),FIND("[",CELL("filename"))+1,255),".xl",REPT(" ",255)),255)),2,3)
 
rollis13 said:
You could use this formula where the last two numbers are First character and Number of characters to pickup:

=MID(TRIM(LEFT(SUBSTITUTE(MID(CELL("filename"),FIND("[",CELL("filename"))+1,255),".xl",REPT(" ",255)),255)),2,3)
Click to expand...

Thanks a lot. It works perfectly.
I will look at your formula in details and learn... :)
 
Just anther thought.
What if I need to insert the first 7 characters of file name to header?
My understanding is the formula won't work in this way.
What is the solution then?
Please :help:...

Thanks.
 
As I know this can be done only by macro, not with formulas.
Supposing that your formula is in cell A1 in your macro, for the center of the header, you can use this line of code:
Code: 
ActiveSheet.PageSetup.CenterHeader = Cells(1, 1).Value
 
Last edited:
Sorry, I should wrote it more clear.
I meant to get the first 7 characters from file name then show them in header instead of subtract them from the formula that you wrote in post#2.
 
rollis13 said:
You could use this formula where the last two numbers are First character and Number of characters to pickup:
Click to expand...
I already told you what to do, so if you need the first 7 characters a quick change to the formula would be:

... 255)),1,7)

or use:

=LEFT(TRIM(LEFT(SUBSTITUTE(MID(CELL("filename"),FIND("[",CELL("filename"))+1,255),".xl",REPT(" ",255)),255)),7)
 
Last edited:
rollis13 said:
=MID(TRIM(LEFT(SUBSTITUTE(MID(CELL("filename"),FIND("[",CELL("filename"))+1,255),".xl",REPT(" ",255)),255)),2,3)
Click to expand...
Sorry, I meant this formula won't work if I paste it to header.
 
I explained that in post #5, you need to create a macro with the Recorder or write a macro with event Workbook.BeforePrint.
 
So there is no formula can do the trick.

I explained that in post #5, you need to create a macro with the Recorder or write a macro with event Workbook.BeforePrint.
Click to expand...
Sorry, it seems a bit more beyond my knowledge...
 
This is an example of what you need to be placed in the vba module 'ThisWorkbook'. It assumes that the cell with the formula that detects the name of the file is in cell A1 of the active sheet you are printing:
Code: 
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.CenterHeader = Cells(1, 1).Value
End Sub
All you had to do was a search in the web and you would have found thousands of examples for Workbook_BeforePrint.
 
Last edited:
rollis13 said:
This is an example of what you need to be placed in the vba module 'ThisWorkbook'. It assumes that the cell with the formula that detects the name of the file is in cell A1 of the active sheet you are printing:
Code: 
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.CenterHeader = Cells(1, 1).Value
End Sub
All you had to do was a search in the web and you would have found thousands of examples for Workbook_BeforePrint.
Click to expand...

Thanks. I will have a try later as I am busy setting up on other files.
Cheers.
 
You must log in or register to reply here.
Back
Top