Daily Archives: 16 October 2018

LibreOffice Calc: Get workbook path only

 in Office  tagged calc / cell / CELL("filename") / char / find / left / len / LibreOffice / substitute by

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

 in Office  tagged calc / cell / CELL("filename") / find / left / LibreOffice by

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

 

LibreOffice Calc: Get workbook filename only

 in Office  tagged calc / cell / CELL("filename") / find / left / LibreOffice / rept / right / substitute / trim by

Get workbook filename only

For Linux and Mac

=TRIM(
  RIGHT(
    SUBSTITUTE(
      LEFT(
        CELL("filename"),
        FIND("#", CELL("filename")) -2
      ),
      "/",
      REPT(" ", LEN(CELL("filename")))
    ),
  LEN(CELL("filename"))
  )
)

For Windows

=TRIM(
  RIGHT(
    SUBSTITUTE(
      LEFT(
        CELL("filename"),
        FIND("#", CELL("filename")) -2
      ),
      "\",
      REPT(" ", LEN(CELL("filename")))
    ),
  LEN(CELL("filename"))
  )
)

The only change is changing the delimiter from / to \.

Qubes 4.0 with Fedora 26: Setup RPM Fusion and ffmpeg

 in GNU/Linux  tagged fedora / fedora 26 / ffmpeg / qubes / qubes 4.0 / qubes-os / rpmfusion by

Recently we wanted to process some media on a Fedora 26 running under a Qubes OS 4.0 installation, we decided to use ffmpeg which is not part of the default repositories but it can be found in the RPM Fusion repositories. To do so, first we updated our system and enabled the RPM Fusion repositories as follows:


sudo dnf update;

sudo dnf upgrade -y;

sudo dnf install https://download1.rpmfusion.org/free/fedora/rpmfusion-free-release-$(rpm -E %fedora).noarch.rpm https://download1.rpmfusion.org/nonfree/fedora/rpmfusion-nonfree-release-$(rpm -E %fedora).noarch.rpm;

sudo dnf config-manager --set-enabled rpmfusion-free rpmfusion-nonfree;

By default DNF on the template VM did not enable the rpmfusion repositories so we had to enable them manually with the last command above or else we would get the following error:

$ sudo dnf install ffmpeg;
Last metadata expiration check: 0:17:49 ago on Tue Oct 16 09:09:22 2018.
No match for argument: ffmpeg

Then, we updated the system once more so that the information from the new repositories would get downloaded to our system and then we performed the installation of ffmpeg. While installing ffmpeg, since it was the first time that we were using the new repositories we were asked to verify the keys that were imported. We were able to manually verify the keys from this page.

The commands used to install ffmpeg are the following:


sudo dnf update;

sudo dnf install ffmpeg;