How to retrieve the name of the excel file into spreadsheet?

[Guest]

Does anyone have any suggestions on how to retrieve the filename of excel
into spreadsheet? for example
The name of working excel file is "Eric - RRRRR.xls", I would like to
retrieve the filename into cell A1, and to count the number of R within
"RRRRR", but ignore the part of the string "Eric - ". It should return 5 in
cell B1.
Does anyone have any suggestions on how to count it?
Thanks in advance for any suggestions
Eric

 

[Bernard Liengme]

File path, file and worksheet name:
=CELL("filename",A1)
File path only:
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
File name only:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)
The sheet name:
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
Restrictions
This technique only works for workbooks that have been saved, at least once.

best wishes

 

[Guest]

Thank you very much for your suggestions
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1), which will return
"Eric - RRRRR.xls"
Do you have any suggestions on how to remove "Eric - " & ".xls" and count
the number of "R" within the string "RRRRR"? and return 5 in cell B1
Thank you for any suggestions
Eric

File path, file and worksheet name:
=CELL("filename",A1)
File path only:
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
File name only:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)
The sheet name:
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
Restrictions
This technique only works for workbooks that have been saved, at least once.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Does anyone have any suggestions on how to retrieve the filename of excel
into spreadsheet? for example
The name of working excel file is "Eric - RRRRR.xls", I would like to
retrieve the filename into cell A1, and to count the number of R within
"RRRRR", but ignore the part of the string "Eric - ". It should return 5
in
cell B1.
Does anyone have any suggestions on how to count it?
Thanks in advance for any suggestions
Eric

 

[Bernard Liengme]

With your formula in A1, this =MID(A1,8,256) will return RRRRR.XLS
So will =MID(your_formula,8,256)
And with the RRRRR.XSL in B1, this returns 5 (number of R's) =LEN(B1)-4

This returns just the R's: =LEFT(MID(A1,8,256),FIND(".",A1)-8)
This tells how many R's: =LEN(LEFT(MID(A1,8,256),FIND(".",A1)-8))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Thank you very much for your suggestions
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1),
which will return
"Eric - RRRRR.xls"
Do you have any suggestions on how to remove "Eric - " & ".xls" and count
the number of "R" within the string "RRRRR"? and return 5 in cell B1
Thank you for any suggestions
Eric

File path, file and worksheet name:
=CELL("filename",A1)
File path only:
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
File name only:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)
The sheet name:
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
Restrictions
This technique only works for workbooks that have been saved, at least
once.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Does anyone have any suggestions on how to retrieve the filename of
excel
into spreadsheet? for example
The name of working excel file is "Eric - RRRRR.xls", I would like to
retrieve the filename into cell A1, and to count the number of R within
"RRRRR", but ignore the part of the string "Eric - ". It should return
5
in
cell B1.
Does anyone have any suggestions on how to count it?
Thanks in advance for any suggestions
Eric