GETSUBSTR Function for LibreOffice Calc

GETSUBSTR() Description

The GETSUBSTR() function is intended for selecting part of the text by the given separator.

The GETSUBSTR function code splits the text into an array by the specified separator and returns the element of the given array according to the specified serial number.

BASIC Code for GETSUBSTR

Below is the macro code to create a custom function GETSUBSTR that can split the text into parts and retrieve any element of it in LibreOffice Calc.

To add a new function, open the menu Tools - Macros - Edit Macros..., select Module1 and copy the following text to the module:

Function GETSUBSTR(Txt, Delimiter, n) As String  
   Dim txtArray As Variant  
     
     If Txt = "" Then Exit Function
     
      txtArray = Split(Txt, Delimiter) 
      maxExt = UBound(txtArray)
      
      If n >= 0 Then 
        normExt = n - 1
      Else      
        normExt = maxExt + n + 1
      EndIf      
       
      extToFind = Max(Min(maxExt, normExt), 0)
      
      GETSUBSTR = txtArray(extToFind)
     
End Function

Then, close Macro Editor, return to LibreOffice Calc and use the new function in any cell.

Using the YouLibreCalc extension

You can also use the GETSUBSTR() function by setting the extension " YouLibreCalc.oxt ". After that, this function will be available in all files that will be opened in LibreOffice Calc.