So far I’ve been unable to find documentation on range.createReplaceDescriptor so if you can point that out to me I’d appreciate it.
I’d like to search ‘cell.value > 1000’ and replace cell.NumberFormat for those cells that the search returns.
Thanks,
Mike
Do you see this option in Find & Replace dialog? Because that’s what functionality the SearchDescriptor represents.
roughly with regex
^\d{4,}$
or ^[1-9]\d{3,}$
or ^0*[1-9]\d{3,}(\.\d+)$
etc … 
mikekaganski,
The link to api.libreoffice.org/docs/* is bookmarked, tho a little terse it will help. I had hoped for a little more detail but it did make the case that it is text only and won’t help with changing cell.FormatNumber.
It led me to the page on XSearchDescriptor but the details are a little slim, I don’t know if the search returns a value, moves the cursor or what. Guess it’s time for a little exploration, nor does there seem to be a ‘Next’.
I’m not sure if it was you or fpy that posted the regex but thanks for that. I hoped to copy the explanation but it’s an image and searching for it here and with duckduckgo or google failed to return the text.
Much obliged for the input.
Be well,
Mike
https://wiki.documentfoundation.org/Documentation/SDKGuide/Text_Search_and_Replace
just a screenshot of gemini; but any AI should get you the same.
and in general:
No, I’m not sure, I’ve already been using that but all the examples I’ve seen use ‘STYLE’ not NumberFormat. I noticed in one of your posts that you suggest STYLE should be depricated. If there is a way to set cell.NumberFormat in a formula I’m all ears.
I was looking at search/replace not realizing such was limited to text, after all most entries in a spreadsheet are numbers.
Be well,
Mike
[====]
Lupp slightly edited the post to make it better readable.
As an Example:
disask120944formatGT1000.ods (62.4 KB)
The CF feature neither assigns a style nor a hard NumberFormat attribute.
It applies the attributes of the conditional style to the cells for the view.
If your version of Calc does not support the function RANDARRAY(), tell me. You will get another example then.
BTW: I never called styles “deprecated”. If you found a post where you think I did, please point me to it. I should correct the mistake.
Here is what I’m running:
Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: x11
Locale: en-US (C); UI: en-US
Raspbian package version: 4:7.4.7-1+rpi1+deb12u5
Calc: threaded
disask120944formatGT1000.ods on my system shows a range of cells with some MicroSoft function in them the butons do nothing and there is no code for it in the basic IDE that I can find.
It’s likely some configuration on my end but macro security is set at itls lowest.
As for the “deprecated” statement I ran across, I’ve looked at so many pages trying to chase down search/replace that I can’t point you to it. As I recall the post was something like '“Please don’t recommend STYLES” with further explanation.
Thanks for trying to help, I’ve gotten enough tips to have finally gotten a Sub written that seems to do what I want.
Be well,
Mike
feel free to share 
You hadn’t mentioned your version earlier.
My example was made using LibO V 25.2
Please note that the version naming has changed: 7.6 was followed by 24.2. Then came the main versions 24.8 and 25.2.
24.8 Calc had some new functions implemented for compatibiliy with recent MS Excel. One of them (RANDARRAY) was used in my example.
NEVER do so! It’s an open door for malign code.
Choose “medium” and check downloaded files from doubtable sources with macros for the absence of malign code before you permit execution.
However, my example was made without any “macros” because there was a suspicion that you had posted disregarding the XY problem. The example demonstrated a solution for “How can I display numbers >1000 using a different format?” You didn’t give an explanation for what reason you thought to need that “format replacement”. After all such a feature wasn:t implemented because well-informed developers tought it wasn’t needed.
If my suspicion was wrong, I would like to get informed about your motives.
New example also runnig under your version:
disask120944formatGT1000_WithoutRANDARRAY.ods (78.2 KB)
Hi Lupp,
That file runs on my system though I admit I’m not bright enough to see how you did it since I didn’t find any basic code under the IDE.
As for my reasons, I’ve been putting conditional formats in some cells so have some limited experience with it. The primary reason I was asking about search and replace was speed using Pitonyak’s rangeWalk() works quite well but it’s interpreted while search/replace are complied in and should be much quicker. Using search and only dealing with those cells that have large numbers has cut down on the time I sit here waiting for the many calcs to finish. That and the fact that large numbers showing with 2 decimals means I get more data on the screen if they are displayed as INTs.
Be well,
Mike
If you’re asking to see the code I’m thinking to use, then here is the meat of it.
Function currentSheet
currentSheet = ThisComponent.CurrentController.ActiveSheet
End Function ’ currentSheet -----------------------------------
’ ================================================
Sub rangeNumFormat
’ from Pitonyak OOME p.503 ’ Format numbers as currency
Dim oDoc : oDoc = ThisComponent
Dim oRange, oSheet
Dim decimals : decimals = Array( 1, 122, 2 , 104, 11, 37)
’ 3 is int w/ commas
’ 1 = noDecimalNoComma
’ 122 = 1DecinalNoComma
’ 2 = 2DecimalNoComma
oSheet = oDoc.getSheets().getByName("Sheet1")
If Not (currentSheet.Name = "Sheet1") Then '
MsgBox("Wrong Sheet Not Sheet1")
Exit Sub
End If
oRange = oSheet.getCellRangeByName("A20:E30")
oRange.NumberFormat = decimals(2)
End Sub ’ numFormatRange ---------------------
’ ===========================================
Sub rangeRandomData()
Dim oDoc : oDoc = ThisComponent
Dim oRange 'The primary range
Dim oSheet
Dim oCell 'Holds a cell temporarily
Dim nR, nc
oSheet = oDoc.getSheets().getByName("Sheet1")
If Not (currentSheet.Name = "Sheet1") Then '
MsgBox("Wrong Sheet Not Sheet1")
Exit Sub
End If
oRange = oSheet.getCellRangeByName("A20:E30")
For nc= 0 To oRange.Columns.getCount-1
For nr = 0 To oRange.Rows.getCount-1
oCell = oRange.getCellByPosition(nc,nr)
oCell.value = 100000 * Rnd ' Range from 0 to 10k
Next nr
Next nc
End Sub ’ rangeRandomData ----------------------
’ ===========================================
Sub rangeSearchRegex()
’ posted by mikekaganski on ask.libreoffice.org
’ roughly with regex
’ ^.\d{4,}$
’ or ^[1-9]\d{3,}$
’ or ^0[1-9]\d{3,}(.\d+)$
’ etc …
Dim oDoc : oDoc = ThisComponent
Dim oRange 'The primary range
Dim oSheet 'The sheet
Dim oCell 'Holds a cell temporarily
Dim oCellAddress 'Row or column cell
Dim sSearchStr As String, sMsg As String
Dim oFound
oSheet = oDoc.getSheets().getByName("Sheet1")
If Not (currentSheet.Name = "Sheet1") Then '
MsgBox("Wrong Sheet Not Sheet1")
Exit Sub
End If
oRange = oSheet.getCellRangeByName("A20:E30")
sSearchStr = "^\d*\d{4,}.\d*$|^\d*\d{4,}$|^0.0$"
’
’ from LibreOffice Calc Basic search and replace macro - Super User
’ I think by JohnSun
’ create a search descriptor, set its parameters SearchRegularExpression
’ and SearchType (1 means “search in values”)
oSearchDescriptor = oRange.createSearchDescriptor()
oSearchDescriptor.SearchRegularExpression = True
oSearchDescriptor.SearchType = 1
oSearchDescriptor.setSearchString(sSearchStr)
oFound = oRange.findAll(oSearchDescriptor)
If Not IsNull(oFound) Then
For Each oCell In oFound.getCells()
oCell.NumberFormat = 1
Next oCell
EndIf
End Sub ’ rangeSearchRegex -------------
’ ============================================
I trapped it to Sheet1 so I don’t trash my stocks spreadsheet.
I’ve not yet got it worked into the spreadsheet just select and running the macros via the IDE but think I’m nearly there as this seems to do what I want.
Hope that answers your question and thank all of you for sharing your thougts.
Be well,
Mike