LibreOffice

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 \.

LibreOffice Calc: get the distinct/unique values in a column or a list of elements 4

 in Applications / Office  tagged calc / distinct / LibreOffice / unique by

These videos demonstrate how to get the distinct/unique values in a column or a list of elements in LibreOffice Calc.

Procedure to get the distinct values in a column or a list of elements:

  1. Select the column (or the list of data) that you want to process
  2. Click on the top menu Data
    then click on the option group More Filters
    and finally on the option Standard Filter...
  3. In the new window, change the Field Name on the first row to - none -
  4. Click on the Options label and enable the No duplications checkbox
  5. Finally press the OK button

Video demonstrating how to get the distinct values in a column:

Video demonstrating how to get the distinct values in a list of elements: