String Processing in LibreOffice Calc Macro with Examples

3 min


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 processes are essential for any macro development.

LibreOffice Calc Macro - Join Data

Let’s put 3 strings like below in a LibreOffice calc sheet and join them.

Joining Strings in Calc

In basic, to join two strings/texts, use the “&” 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

Let’s put some more strings in Column A as below in LibreOffice. We will use these three strings for the rest of the processing in this tutorial.

Left

The left function returns the number of characters from a string from the left side. The number of characters 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 the left function above, the right returns the given no of characters from the right side. With the same example, in this case, the 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

The mid function returns any size of the sub-string from a given string. The argument 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 the 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

The 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 characters present i.e. the length of a string.
Usage: Len(string)

Instr

Instr function is used to search a string within a string. If a match is 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 the below link for the complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index


We bring the latest tech, software news and stuff that matters. Stay in touch via Telegram, Twitter, YouTube, and Facebook and never miss an update!

Join our Telegram channel and stay informed on the move.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.


Arindam

Creator of debugpoint.com. All time Linux user and open-source supporter. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

14 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
14
0
Would love your thoughts, please comment.x
()
x