I am using Ubuntu 14.04, 16.04 and 18.04. LibreOffice version 4.x to 6.x. There is a data like New Zeland Vs South Africa in a cell I need a formula which will extract data like New Vs South means first word of the cell then Vs then first word after Vs
1 Like
Hallo
=REGEX(A1;"^(\w+).* (Vs) (\w+).*";"$1 $2 $3")
quite old … but REGEX should work since LO6.4
1 Like
May be something like this…
=REGEX(A1,"^(\w+).* Vs (\w+).*","$1 Vs $2")
2 Likes
Thanks a lot it works
What if sometimes there is a single space before and after Vs and sometines diuble space before and after Vs. Please suggest a single formula that works in both cases.
one or more space is \s+ so the updated function:
=REGEX(A1,"^(\w+).* Vs\s+(\w+).*","$1 Vs $2")
Suggest me what changes should be done in this function.
Function ExtractVsParties(text As String) As String
Dim regex As Object
Dim matches As Object
Dim pattern As String
Dim result As String
' Regular expression pattern similar to the REGEX formula
pattern = "((?:(?:\w+\.|M/s|Mr\.|Shri|Mrs\.|P|PSO|Sk|P S O|P S|Md|Sau|Sd|Dr|Ms|Smt|The|Sk|SK) )?\w+).* Vs ((?:(?:\w+\.|M/s|Mr\.|Shri|Mrs\.|Sk|Md|Sau|Sd|Dr|Ms|Smt|The|Sk|SK) )?\w+).*"
' Create the regex engine
Set regex = createUnoService("com.sun.star.util.TextSearch")
Dim opts As New com.sun.star.util.SearchOptions
opts.searchString = pattern
opts.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
opts.transliterateFlags = com.sun.star.i18n.TransliterationModules.IGNORE_CASE
regex.setOptions(opts)
' Execute regex match
matches = regex.searchForward(text, 0, Len(text))
If matches.subRegExpressions > 2 Then ' match 0 is the whole found text; plus two submatches
result = Mid(text, matches.startOffset(1) + 1, matches.endOffset(1) - matches.startOffset(1)) & " Vs " & Mid(text, matches.startOffset(2) + 1, matches.endOffset(2) - matches.startOffset(2))
Else
result = text ' Return original text if no match is found
End If
ExtractVsParties = result
End Function