TOP

Sentence Case Function for LibreOffice Calc

YLC Utilities logo

SENTENCECASE() Description

The SENTENCECASE() function is designed to convert existing text into sentence format and returns a set of words in the form: "Sentence case function" .

Sentence Case breaks lines separated by periods, exclamation points, or question marks into separate sentences. Then, converts all words to lower case except for the first letter in the sentence and then combines all elements into one continuous text.

The SENTENCECASE() function can be useful for correcting the case of letters in sentences throughout the text.


StarBASIC Code for SENTENCECASE()

Here is the macro code to create a custom function that can case-justify words in sentences (Sentence Case) in LibreOffice Calc.

Open the menu Tools - Macros - Edit Macros..., select Module1 and copy the following text into the module:

Function SentenceCase(ByVal str As String) As String
  'moonexcel.com.ua
  Dim sentences As Variant
  Dim i         As Integer
  Dim FCalc     As Object
    
  FCalc = CreateUnoService("com.sun.star.sheet.FunctionAccess")    
  
  str = Replace(str,"-"," ")
  str = Replace(str,"_"," ")
  str = FCalc.callFunction("TRIM", Array(str))
  str = LCase(str)
  str = FCalc.callFunction("REGEX", Array(str,"([.!?])(\s)(\w)","$1#$3","g"))
  
  sentences = Split(str, "#")
    
  For i = LBound(sentences) To UBound(sentences)       
    sentences(i) = UCase(Left(sentences(i), 1)) & Mid(sentences(i), 2)       
  Next i
    
  SentenceCase = Join(sentences," ")
End Function

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

Using the extension

You can also use the feature SENTENCECASE() by installing the free extension YouLibreCalc.oxt or its full-featured version YLC_Utilities.oxt .

After that, this function will be available in all files that will be opened in LibreOffice Calc.