Monthly Archives: October 2018
IEEE Region 8 – Belgrade Meeting 2018 Mobile Application – Privacy Policy
We don’t collect or share personal information.
LibreOffice Calc: Get workbook path only
Get workbook path only
For Linux and Mac
=LEFT( CELL("filename"), FIND( CHAR(1), SUBSTITUTE( CELL("filename"), "/", CHAR(1), LEN(CELL("filename")) - LEN( SUBSTITUTE( CELL("filename"), "/", "" ) ) ) ) -1 )&"'"
For Windows
=LEFT( CELL("filename"), FIND( CHAR(1), SUBSTITUTE( CELL("filename"), "\", CHAR(1), LEN(CELL("filename")) - LEN( SUBSTITUTE( CELL("filename"), "\", "" ) ) ) ) -1 )&"'"
Bonus
If you want to remove the '
characters as well that are around the filename and path use the following solution, this will allow you to create hyperlinks for the folders as well
=REPLACE( LEFT( CELL("filename"), FIND(CHAR(1), SUBSTITUTE( CELL("filename"), "/", CHAR(1), LEN(CELL("filename")) - LEN( SUBSTITUTE( CELL("filename"), "/", "" ) ) ) ) -1 ) ,1,1,"" )
To create hyperlink as well, use the following
=HYPERLINK( REPLACE( LEFT( CELL("filename"), FIND(CHAR(1), SUBSTITUTE( CELL("filename"), "/", CHAR(1), LEN(CELL("filename")) - LEN( SUBSTITUTE( CELL("filename"), "/", "" ) ) ) ) -1 ) ,1,1,"" ) )
LibreOffice Calc: Get workbook full path with filename
Get workbook full path with filename
=LEFT(CELL("filename"),FIND("#",CELL("filename"))-1)
Explanation
If you want to get the workbook filename and path, you can do so with a formula that uses the LEFT
and the FIND
function.
The CELL("filename")
function is used to get the full file name and path along with current sheet link:
The result will look like this:
'file:///home/user/path/documentName.ods'#$sheetName
Using the FIND
function we find the position of the delimiter #
and then using the LEFT
function, we extract just the full directory path with the filename. We know the number of characters to extract by locating the the position of the hash #
character with FIND
and then subtracting 1 (to exclude itself as well).
Extra
If you want to remove the '
characters as well that are around the filename and path use the following solution
=REPLACE( LEFT( CELL("filename"), FIND( "#", CELL("filename") )-2 ), 1, 1,"" )