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

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

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

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


"Eric" wrote in message
...
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

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

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

"Bernard Liengme" wrote:

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


"Eric" wrote in message
...
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

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

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

"Eric" wrote in message
...
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

"Bernard Liengme" wrote:

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


"Eric" wrote in message
...
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