Using regex in a libreoffice calc macro to extract text from parentheses in a cell

Question

Using Libreoffice 3.5.7.2 on Ubuntu 12.04.

I have text in calc cells in the form of: (IBM) Ibm Corporation.

I am trying to use regex to extract the text between the ()'s using a basic macro. This is what I have tried so far.

Sub getMktValue()
  Dim oDoc as Object
  Dim oSheet as Object
  Dim oCell as Object

  oDoc = ThisComponent
  oSheet = oDoc.Sheets.getByName("Income")
  'regex test code'
  oCell = oSheet.getCellByPosition(0, 1)
  stk = oCell.String()  
  myRegex = oCell.createSearchDescriptor
  myRegex.SearchRegularExpression = True
  myRegex.SearchString = "\((.*)\)"  '"[\([A-Z]\)]" "\(([^)]*)\)" "\(([^)]+)\)"'
  found = oCell.FindFirst(myRegex)
  MsgBox found.String
End Sub

The myRegex.SearchString line contains the various versions I have tried. The result is always the same. The entire contents of the cell are returned not just the text between the ()'s. Is there a way to extract just the text between the ()'s?

Thanks, Jim

Answer 1

The method you tried, .FindFirst, finds in an XSearchable (such as a spreadsheet or range) the first occurrence of the SearchString.

If you want to search within a string value, then you need a different service, com.sun.star.util.TextSearch.

Sub getMktValue()
  Dim oDoc as Object
  Dim oSheet as Object
  Dim oCell as Object

  oDoc = ThisComponent
  oSheet = oDoc.Sheets.getByName("Income")
  'regex test code'
  oCell = oSheet.getCellByPosition(0, 1)
  stk = oCell.getString() 

  oTextSearch = CreateUnoService("com.sun.star.util.TextSearch")
  oOptions = CreateUnoStruct("com.sun.star.util.SearchOptions")
  oOptions.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
  oOptions.searchString = "\((.*)\)"
  oTextSearch.setOptions(oOptions)
  oFound = oTextSearch.searchForward(stk, 0, Len(stk))
  sFound = mid(stk, oFound.startOffset(0) + 1, oFound.endOffset(0) - oFound.startOffset(0))
  MsgBox sFound
  sFound = mid(stk, oFound.startOffset(1) + 1, oFound.endOffset(1) - oFound.startOffset(1))
  MsgBox sFound
End Sub

Greetings

Axel