LibreOffice Calc macro to select specific cells, find regular expressions and replace

English
#1

I regularly import data into Calc. However, the format of that data needs to be modified each time before I am able to analyze the data.

The routine is to select specific parts of the workbook containing the imported data, open the “Find & Replace” window, check the “current selection only” and “regular expressions” boxes, enter my find criteria which is “^\s*(([[:digit:].]+)\s*%\s*)\s*$” and enter the replace criteria which is “-$1%”, then click “replace all”. I then repeat by searching for “^.” and replace with “&”, again clicking “replace all”.

This is a tedious process which I am doing dozens of times each and every day.

Is it possible to create a macro to perform this for me quickly and easily?

#2

Welcome!
Yes, it is possible, and quickly and easily.
Download this document - Useful Macro Information For OpenOffice.org By Andrew Pitonyak. So far, you don’t need to read all of it, now you only need 6.19. Select a named range and 7.14.4. Search and Replace with Attributes and Regular Expressions. Possibly also 6.2. Display cell value, string, or formula, just to understand the chain of action to get ThisComponent → Sheets → Sheet → CellByposition (or CellRangeByposition, or CellRangeByName). You just need to combine the listings from these chapters.

(This is in case you really need to solve the XY problem and write a macro to clean up the data after an incorrect import. And if you solve your real problem, then its description should be similar to “every day I download data from the site (from a text file, from a table from PDF) and one of the fields that contains percentage values is loaded as AAA or BBB text. To process this data, I need to get it in the form of real numerical values”)

#3

Thank you for the prompt response and for the link to this mammoth document.

I have taken a look, and this is written for developers. I am well versed in using spreadsheets (LibreOffice Calc and of course Excel), but I have never written any VBA, Python or any other code.

I am struggling to make any sense of these instructions.

So, while you suggest that it is quickly and easily possible to create the macro, I fear that is not the case for me.

Are there any other options available for me?

#4

Yes, the second part of my comment, which is in italics. Describe where you are importing data from, how (external data, copy-paste, inserting a sheet), what exactly is not suitable for further processing (a screenshot is possible, but it is better to attach a sample file)

#5

I have attached my spreadsheet.

It has multiple tabs, but the tab that I use for importing data is the one labelled “Extended TIKR”

I have removed all of the logic and data from the other tabs in order to reduced the file size and to protect confidential data. So all other worksheet tabs are blank.

On the Extended TIKR tab you will see the data in the form that I import it. I have shaded in yellow the cells that I need to select for this “find and replace” operation. They are:
b3:n71
q3:ac71
af3:ar71
au3:cq71

My process:

  • select the above cells

  • open “Find & Replace”

  • check the “current selection only” and “regular expressions” boxes

  • enter my find criteria which is ^\s*(([[:digit:].]+)\s*%\s*)\s*$ and enter the replace criteria which is -$1%

  • click “replace all”

  • I then repeat with find criteria ^. and replace with &

  • click “replace all”

sample spreadsheet.ods (153.3 KB)

Thank you in advance

#6

If I understand your regular expression correctly, you could simply check options “Detect special numbers” in the text import dialog and the percent values will import as numbers.

1 Like
#7

I manually copy and paste the data, already in table format, into my spreadsheet. It isn’t imported as a CSV unfortunately, so this option doesn’t provide me with a solution.
Otherwise it would have been a good idea. Thank you for the thought.

#8

Paste-special as unformatted text and you have all the import options availlable.

#9

It is the wierdest thing. I can do that, and have tried it previously, but negative percentages appear as bracketed numbers with a percentage sign, so minus seventeen percent will display as (17%). If I right click and “format cells”, it shows that cell formatted as a number. However, it is impossible to perform any calculations on it. I get a #VALUE! error.

This is why I do my “find & replace” which converts (17%) into -0.17 and hey presto, I can run calculations on it.

Any ideas?

I think it is just the way that the data is formatted at source. The insertion of a % sign with the numbers confuses LibreOffice. But my “find & replace” trick solves the problem. It is just tedious to do all of the time.

This is why I am looking for a solution to make my life a little easier

#10

No, it’s a format error… Unlike Excel, Calc doesn’t know that negative percentages can be written in parentheses. Perhaps this is temporary. Nevertheless, something becomes clear with the source of the problem.

Excel Style

Tell me, in what program do you open the table from which you take data for copying? Also in Calc? So this is an Excel import filter error that should be reported to the developers.

Well, until the problem is fixed, I’ll probably have to help you with writing a macro. Your search and replace strings didn’t work correctly for me - probably due to my system regional settings. Nevertheless, I want to clarify - and if you do not pre-select individual ranges, but perform a replacement for the entire sheet - will this change something?

#11

A recorded macro with modified regex \(escaped outer braces\):

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

Sub Main

End Sub


sub recorded_Replace
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(20) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.SearchFiltered"
args1(4).Value = false
args1(5).Name = "SearchItem.Backward"
args1(5).Value = false
args1(6).Name = "SearchItem.Pattern"
args1(6).Value = false
args1(7).Name = "SearchItem.Content"
args1(7).Value = false
args1(8).Name = "SearchItem.AsianOptions"
args1(8).Value = false
args1(9).Name = "SearchItem.AlgorithmType"
args1(9).Value = 1
args1(10).Name = "SearchItem.SearchFlags"
args1(10).Value = 71680
args1(11).Name = "SearchItem.SearchString"
args1(11).Value = "^\s*\(([[:digit:].]+)\s*%\s*\)\s*$"
args1(12).Name = "SearchItem.ReplaceString"
args1(12).Value = "-$1%"
args1(13).Name = "SearchItem.Locale"
args1(13).Value = 255
args1(14).Name = "SearchItem.ChangedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.DeletedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.InsertedChars"
args1(16).Value = 2
args1(17).Name = "SearchItem.TransliterateFlags"
args1(17).Value = 1073743104
args1(18).Name = "SearchItem.Command"
args1(18).Value = 3
args1(19).Name = "SearchItem.SearchFormatted"
args1(19).Value = false
args1(20).Name = "SearchItem.AlgorithmType2"
args1(20).Value = 2

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())


end sub
#13

Sorry for the delay in my response.

I take the data from a third party application which is in an HTML format that I can copy into Calc

In answer to your other question, the “find and replace” function that I seek to perform is in part to turn text into numbers. If I select the entire sheet (including column and row titles that are supposed to be in text) then it seems to corrupt the text fields making Vlookup functionality malfunction

#15

Excuse my IT ignorance, but I am not sure what to do with this code. I have tried to paste it into my macros and to run it, but nothing happens.

#16

It’s not scary, the main thing is that you answered (and did not leave offended) :grinning:

Oh, in that case, it really won’t be possible to improve the import

Oh yes, this is indeed a danger. But since there are no numbers in the text names (in any case, I didn’t see them), this obstacle is easily bypassed by a preliminary search for [: digit:] and Find all - cells will be selected that are subject to further processing using the other two replacements. By the way! What does the second replacement do with the search string “there is one character from the beginning of the string”?

#17

The first converts a negative percentage in parenthesis into a negative percentage number, so (-17%) becomes -0.17

The second converts any fields that were pasted as text into numbers (when pasting from HTML to Calc it is amazing how many times this happens)

#19

Yes I understand. I’m just used to a different spelling .+

Regular_Expressions_in_Calc

#12

Actually the macro that does the actions you described in your question is not very complex:

Option Explicit 
Sub replaceTextWithNumbers()
Const SHEET_NAME = "Extended TIKR"
Dim oSheets As Variant, oSheet As Variant
	oSheets = ThisComponent.getSheets()
	If Not oSheets.hasByName(SHEET_NAME) Then Exit Sub 
	oSheet = oSheets.getByName(SHEET_NAME)
	replaceInRange(oSheet.getCellRangeByName("B3:N71"))
	replaceInRange(oSheet.getCellRangeByName("Q3:AC71"))
	replaceInRange(oSheet.getCellRangeByName("AF3:AR71"))
	replaceInRange(oSheet.getCellRangeByName("AU3:CQ71"))
End Sub

Sub replaceInRange(aRange As Variant)
Dim oRDescriptor As Variant 
	oRDescriptor = aRange.createReplaceDescriptor()
	oRDescriptor.SearchRegularExpression=True
	oRDescriptor.setSearchString("^\s*\(([[:digit:].]+)\s*%\s*\)\s*$")
	oRDescriptor.setReplaceString("-$1%")
	aRange.replaceAll(oRDescriptor)
	oRDescriptor.setSearchString("^.")   ' or ".+"? '
	oRDescriptor.setReplaceString("&")
	aRange.replaceAll(oRDescriptor)
End Sub
#14

I get a “BASIC runtime error. Sub-procedure or function procedure not defined” error message with this line highlighted:
replaceInRange(oSheet.getCellRangeByName(“B3:N71”))

I then tried to manually select the cells and to run the “replace in range” macro, but then I get “BASIC runtime error. Argument is not optional” with this line highlighted:
oRDescriptor = aRange.createReplaceDescriptor()

What do I need to do?

#18

The replaceInRange() procedure does not need to be done manually - the replaceTextWithNumbers() procedure will do this for each of the four ranges. That is, it is enough to execute only replaceTextWithNumbers - and all the work will be done.
Weird error message… Did you put all the code, the text of both procedures in one module?

#20

I cut and pasted the macro code into my macros. It records both parts as separate modules (maybe because there are two “Sub” / “End Sub” parts? (I am guessing because I have no idea).

How do I combine into a single module?

I just tried to run the replaceTextWithNumbers() procedure and I still get “BASIC runtime error. Argument is not optional” with this line highlighted:
oRDescriptor = aRange.createReplaceDescriptor()

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.