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,""
  )
)

This post is also available in: Αγγλικα