How can I automatically remove specific text cells after sorting a column?

Osmario · July 18, 2026, 11:51am

Title: How can I automatically remove specific text cells after sorting a column?

I have a column in LibreOffice Calc (column F) containing a mixture of numeric values and text entries such as “R… 11h”.

My workflow is:

Paste the clipboard contents into cell F1.
Sort column F in ascending order.
After sorting, all numeric values are grouped at the top, while all cells containing “R… 11h” are grouped at the bottom.

My goal is to automatically clear the contents of the cells containing “R… 11h”, leaving only the numeric values in the column.

The number of rows varies each time, so the solution should not depend on a fixed range.

Is there a way to accomplish this using:

A formula;
A built-in LibreOffice Calc feature or command;
Any other method that does not require programming?

If none of these approaches is possible, I would also appreciate a macro solution.

Thank you very much for your help.

Lupp · July 18, 2026, 3:27pm

You need to clarify:

Has the mentioning of the specific column F any actual meaning or might you work with any column?
Is the example string R...11h playing any specific role, or is it just “any text”?
Why do you talk of specific text cells?
Is the sorting an actually needed step, or shall it just help to separate numbers from texts?
Are you aware of the fact that removing cells and clearing (deleting) cell contents are very different processes?
Do you see clearly that a formula in a cell never can remove or clear anything elsewhere?

CRDF · July 18, 2026, 10:59pm

Check this code.
Manually pasting the content into F
Sources:

Google AI;
@Lupp data sample with A5 Empty.

Option Explicit
REM Ctrl - Right Click ANY CELL
Sub EliminarAlfanumericos(Evt As Object)
	Dim oSheet As Object, oRange As Object
    Dim oSortFields(0) As New com.sun.star.table.TableSortField
    Dim oSortDesc(1) As New com.sun.star.beans.PropertyValue
	On Error GoTo Erro
	REM SET VARIABLES FOR SHEET AND THE F COLUMN:
	oSheet = ThisComponent.CurrentController.ActiveSheet
    oRange = oSheet.getCellRangeByName("F:F")
    REM CONFIGURE THE SORTING:
    oSortFields(0).Field = 0 ' sort by the 1st column in the range
    oSortFields(0).IsAscending = True
    oSortFields(0).IsCaseSensitive = True
    REM CONFIGURE THE SORT DESCRIPTOR:
    oSortDesc(0).Name = "SortFields"
    oSortDesc(0).Value = oSortFields()
	oSortDesc(1).Name = "ContainsHeader"
    oSortDesc(1).Value = False ' no headers    
	REM SORT RANGE:
	oRange.sort(oSortDesc())
	ClearNonNumeric(oSheet)
	Exit Sub
	Erro: MsgBox "ERRO " & Error & Chr(10) & "na linha " & Erl
End Sub
REM CLEAR NON NUMERIC CONTENTS
Sub ClearNonNumeric(Sheet As Object)
	Dim FCell As Object
	Dim r As Integer : r = 0	
	On Error GoTo Erro
	FCell = Sheet.getCellByPosition(5, r) ' [F] = 5
    Do While FCell.Type <> com.sun.star.table.CellContentType.EMPTY
    	REM IF NOT NUMERIC CLEAR CONTENT = STRING:
    	If FCell.Type <> com.sun.star.table.CellContentType.VALUE Then FCell.clearContents(4)
    	r = r + 1
    	FCell = Sheet.getCellByPosition(5, r)
    Loop	
	Exit Sub
	Erro: MsgBox "ERRO " & Error & Chr(10) & "na linha " & Erl
End Sub

yclik · July 18, 2026, 2:45pm

Helo
a suggestion with a macro

Sub ColFtri	
Dim oDoc as Object	
Dim Sheets as object
Dim Chaine as String	
	
oDoc=ThisComponent	
Sheet = oDoc.Sheets.getByName("Feuille1")

For i=2 to 5 
Adress="F"+i
Chaine=Sheet.getCellRangeByName(Adress).String 'lire cellule B40	
	If IsNumeric(chaine)then goto Continued else Sheet.getCellRangeByName(Adress).ClearContents(com.sun.star.sheet.CellFlags.STRING
Continued:
Next i	

end Sub

For i=2 to 5 for row 2 to 5
replace 5 by your necessary row
tricolf.ods (11.5 KB)

koyotak · July 18, 2026, 3:13pm

another solution with auxiliary column and sorting formula within a matrix structure
CYT57_LO-CALC_sort-cols_051322.ods (11.2 KB)

Wanderer · July 18, 2026, 3:49pm

As this is a thread with several answers I continue this way:

It is not possible to remove something by formula
If it is ok to have your values invisible look for conditional formatting. (I would suggest to show this cells in grey on grey, so you remember there is some content, but can ignore it.
My idea would be to use a filter and write the filtered content to another sheet. The second sheet would only have the needed cells/rows. Note you can also refresh this filters after you pasted new content to the first sheet.
I don’t know a way to remove content without macro (aka programming).

So my suggestion is: Don’t remove, but try to make the values invisible…

Lupp · July 18, 2026, 6:58pm

... pointing out 2 ways:
One without user code needing some interaction which is described just so:
disask_136474_SpecialFilteringAndSortingInteractively.ods (30.4 KB)

One basded on user code:
disask_136474_SpecialFilteringByUserCode.ods (16.6 KB)

(I would prefer this one.)

yclik · July 19, 2026, 8:41am

hello

You can use conditionnal format with font color white (if backgroud of cell is white

Osmario · July 19, 2026, 5:30pm

After a long and rather epic journey, the problem has finally been solved!

I would like to express my sincere gratitude to everyone who took the time to read my questions, test ideas, write code, suggest improvements, and patiently share their knowledge.

Each suggestion, even when it was not the final solution, helped me better understand the problem and brought me one step closer to the answer. The final macro is the result of a collective effort, and I truly appreciate the generosity and expertise of this community.

Thank you, Carlos Florentino, JJH, Lupp, and everyone else who contributed along the way. Your willingness to help a stranger is what makes communities like Ask LibreOffice so valuable.

For the benefit of anyone who may encounter the same problem in the future, I am also sharing the final version of the macro that successfully solved it.

Option Explicit

Sub OrganizarColunaF

    Dim oDoc As Object
    Dim oSheet As Object
    Dim oCursor As Object
    Dim oCell As Object
    Dim oRange As Object

    Dim numeros() As Double
    Dim ultimaLinha As Long
    Dim quantidade As Long
    Dim linha As Long
    Dim texto As String

    oDoc = ThisComponent
    oSheet = oDoc.CurrentController.ActiveSheet

    ' Determine the last used row.
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(True)
    ultimaLinha = oCursor.RangeAddress.EndRow

    ' Allocate space for numeric values.
    ReDim numeros(0 To ultimaLinha)

    quantidade = 0

    ' Scan column F starting at F1.
    For linha = 0 To ultimaLinha

        oCell = oSheet.getCellByPosition(5, linha)
        texto = Trim(oCell.String)

        If texto <> "" Then
            If IsNumeric(texto) Then
                numeros(quantidade) = CDbl(texto)
                quantidade = quantidade + 1
            End If
        End If

    Next linha

    ' Clear the old contents of column F.
    oRange = oSheet.getCellRangeByPosition(5, 0, 5, ultimaLinha)

    oRange.clearContents( _
          com.sun.star.sheet.CellFlags.VALUE _
        + com.sun.star.sheet.CellFlags.DATETIME _
        + com.sun.star.sheet.CellFlags.STRING _
        + com.sun.star.sheet.CellFlags.FORMULA)

    ' Sort the numbers.
    If quantidade > 1 Then
        OrdenarNumeros numeros(), quantidade
    End If

    ' Rewrite the sorted numbers from F1 downward.
    For linha = 0 To quantidade - 1
        oCell = oSheet.getCellByPosition(5, linha)
        oCell.Value = numeros(linha)
    Next linha

    ' Return the cursor to F1.
    oDoc.CurrentController.select( _
        oSheet.getCellRangeByName("F1"))

End Sub

Sub OrdenarNumeros(numeros() As Double, quantidade As Long)

    Dim intervalo As Long
    Dim i As Long
    Dim temporario As Double

    intervalo = quantidade \ 2

    Do While intervalo > 0

        For i = intervalo To quantidade - 1

            temporario = numeros(i)
            Dim j As Long
            j = i

            Do While j >= intervalo

                If numeros(j - intervalo) <= temporario Then
                    Exit Do
                End If

                numeros(j) = numeros(j - intervalo)
                j = j - intervalo

            Loop

            numeros(j) = temporario

        Next i

        intervalo = intervalo \ 2

    Loop

End Sub

Please accept my heartfelt thanks and my deepest appreciation.

With my best regards,

Osmario Avila