String Processing in LibreOffice Calc Macro with Examples

Advertisement

In this tutorial, we will show how to do various common string processing using macro in LibreOffice. We will use LibreOffice Calc cells to use the strings for this tutorial. These processings are essential for any macro development.

Lets put 3 strings like below in a LibreOffice calc sheet and join them.
LibreOffice Calc Macro - Join Data

Joining Strings in Calc

In basic, to join two strings/texts, use “&” operator. We will use this to join three columns of strings and put the results in another column.

Sub String_join()
  part1 = ThisComponent.Sheets(0).getCellByPosition(0,1)
  part2 = ThisComponent.Sheets(0).getCellByPosition(1,1) 
  part3 = ThisComponent.Sheets(0).getCellByPosition(2,1)

  result_cell = ThisComponent.Sheets(0).getCellByPosition(3,1)
  result_cell.String = part1.String & "." & part2.String & "." & part3.String
End Sub
LibreOffice Calc Macro - Join

LibreOffice Calc Macro – Join

Lets put some more strings in Column A as below in LibreOffice. We will use these three strings for rest of the processings in this tutorial.

Left

Left function returns the number of characters from a string from left side. The number of character can be specified in the argument of the function. Put a string in cell A1 – “www.google.com” and run the function on the A1 cell as below. This function will return sub-string “www”.

Sub left_demo()
  part1 = ThisComponent.Sheets(0).getCellByPosition(0,0)
  result = ThisComponent.Sheets(0).getCellByPosition(2,0)
  result.String = left(part1.String,3)	
End Sub
LibreOffice Calc Macro - Left Example

LibreOffice Calc Macro – Left Example

Right

Just like left function above, right returns the given no of character from right side. With the same example, in this case, below function would return “com”.

Sub right_demo()
  part1 = ThisComponent.Sheets(0).getCellByPosition(0,0)
  result = ThisComponent.Sheets(0).getCellByPosition(2,0)
  result.String = right(part1.String,3)	
End Sub
LibreOffice Calc Macro - Right Example

LibreOffice Calc Macro – Right Example

Mid

Mid function returns any size of sub-string from a given string. The arguments of this function is Mid(string, start position, length of the sub-string). To have Mid function in works, put some strings in a column in LibreOffice calc and run the Mid function. In this below example, the strings from A columns is converted to sub-string which is in column B. The sub-string is starting from character 4 of the string and length is 6 characters.

Sub mid_demo()
  part1 = ThisComponent.Sheets(0).getCellByPosition(0,1) 
  result = ThisComponent.Sheets(0).getCellByPosition(1,1)
  result.String = mid(part1.String,5,6) 
  
  part2 = ThisComponent.Sheets(0).getCellByPosition(0,2)
  result = ThisComponent.Sheets(0).getCellByPosition(1,2)
  result.String= mid(part2.String,5,6) 
  
  part3 = ThisComponent.Sheets(0).getCellByPosition(0,3)
  result = ThisComponent.Sheets(0).getCellByPosition(1,3)
  result.String = mid(part3.String,5,6) 
End Sub
LibreOffice Calc Macro - Mid Example

LibreOffice Calc Macro – Mid Example

Trim

Trim function is used to remove leading and trailing spaces from a string.
The function can be used like this: Trim(your_string_here)

Len

Len function returns the number of character present i.e. the length of a string.
Usage: Len(string)

Instr

Instr function is used to search a string within a string. If match found, Instr returns the starting position number of the searched string and if not found, it returns 0.

Sub instr_demo()
  part1 = ThisComponent.Sheets(0).getCellByPosition(0,1) 
  result = ThisComponent.Sheets(0).getCellByPosition(1,1)
  result.Value = instr(part1.String,"oogl") 
  
  part2 = ThisComponent.Sheets(0).getCellByPosition(0,2)
  result = ThisComponent.Sheets(0).getCellByPosition(1,2)
  result.Value = instr(part2.String,"dd") 
  
  part3 = ThisComponent.Sheets(0).getCellByPosition(0,3)
  result = ThisComponent.Sheets(0).getCellByPosition(1,3)
  result.Value = instr(part3.String,"untu")   	
End Sub
LibreOffice Calc Macro - Instr Example

LibreOffice Calc Macro – Instr Example

In this tutorial, basic string processing is covered which can be used in LibreOffice Macro.

 

Looking for Something Else?

If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index

You may also like...

Translate »