For any of these to work the workbook must be saved first

=CELL("filename",A1) - Full file path, including worksheet name

=LEFT(CELL("filename",A1), FIND("]",CELL("filename", A1))) - Full file path

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1)) - 2) - Path only

=MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1))- 1), FIND("[",CELL("filename", A1))+1, 255) - File name only

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) - Worksheet name only