CALC REGEX search and replace

isgoren · September 22, 2020, 5:00pm

I am trying to convert the first alpha letter in a cell from lower case to upper case
here’s my regex:

find: ^[:alpha:]

replace: [:upper:]

image description

finds the first lowercase letter correctly…
but replaces it with the text [:upper:] instead of replacing the character with the uppercase character.
why is it doing this?
How do I make it replace with the upper case character ???

igorlius · September 22, 2020, 7:43pm

please add you OS and LO version.

isgoren · September 22, 2020, 7:56pm

Ubuntu 20.04.1 LTS

Release: 20.04

Codename: focal

Libre Office: Version: 6.4.5.2

igorlius · September 22, 2020, 8:10pm

Not sure if LO is actually capable of this. Based on this

src: Find & Replace

i would say the regex can only be used in the “search”, but i could be wrong.

Since you are under linux, you could also use “sed” to change the case of certain words.

Example:

echo -e libreoffice | sed "s/\(libreoffice\)/\u\1/g"

anon73440385 · September 22, 2020, 8:23pm

May be =PROPER(LEFT(A1;1) & RIGHT(A1;LEN(A1)-1)) in a helpers column could help you ([:upper:] is not a function converting text to uppercase).

erAck · September 22, 2020, 8:17pm

Regular expressions are matches, not transformations. The replacement string is literal unless it contains references to capture-group text from the find.

Lupp · September 22, 2020, 10:29pm

As far as I can tell there are no non-alpha letters, and characters that not are alpha don’t have a “case” in the needed sense.
Therefore the question actually asks for the conversion of the first character of text contained in cells to be converted to upper case if possible at all.

Put the Basic “macro” posted below in an appropriate module.

Select the cell ranges in your sheet you want to be processed and call the macro. All the changes made to the elected cells can be undone by a single Ctrl+Z then. Cells not containing text will be skipped anyway.

REM  *****  BASIC  *****

Sub goThroughSelectionTextCellsMakeUpperChar1(Optional pDoc As Object)
If IsMissing(pDoc) Then pDoc = ThisComponent
rgs = pDoc.CurrentSelection.queryContentCells(4)
uMgr = pDoc.UndoManager
uMgr.enterUndoContext("Char1 to upper case")
  For Each rg In rgs
    da = rg.getDataarray()
    uR = Ubound(da) : uC = Ubound(da(0))
    For r = 0 To uR
      For c = 0 To uC
        Mid(da(r)(c), 1, 1, Ucase(Left(da(r)(c), 1)))
      Next c
    Next r
    rg.setDataArray(da)
  Next rg
uMgr.leaveUndoContext
End Sub