[Solved] Find&Replace regular expressions are not recognized

Discuss the spreadsheet application

[Solved] Find&Replace regular expressions are not recognized

Postby TToth » Fri Jan 11, 2019 4:49 pm

[Solved] see Lupp's answer for explanation

I already search through numerous topics but not one of them matched my problem.

I have Alternative Search installed

It can find characters with regex, no problem, but when I try to replace something with regex it doesn't recognize it and replaces the text with plain text regex expression.

To be clear, I have this cell:
  • Reqs
data1, data2

And I want it to look like this:
  • Reqs

But when a try to replace the ", " with "\n" i get:
  • Reqs

When I do it manually and search for the "\n" character it is found without problem.
I have ONLY regex selected in the Options and even enabled them in formulaes.


    [\n], \x0D\x0A, \x0D, \x0A, with and without "0", with u and x \u0A
    So no regular expression format is recognized in the replace field
Everything was inserted as plain text and I'm staring to lose it :knock: :knock:

Moving them into separate rows manually would be too much hassle and as far as I can tell there's no opition for separating celltexts at characters and moving them to another row without overwriting the data below AND merging the rows other than the "reqs" ones automatically. But would be a solution nevertheless
Last edited by TToth on Sat Jan 12, 2019 9:43 pm, edited 3 times in total.
OpenOffice 4.1.6 | Windows 10 Edu x64
Posts: 2
Joined: Fri Jan 11, 2019 4:22 pm

Re: Find & Replace regular expressions are not recognized

Postby erbsenzahl » Sat Jan 12, 2019 11:35 am

https://wiki.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Calc wrote:Using '\n' in the 'Replace with' box will replace with the literal characters '\n', not a hard line break.

In a formula a hard line break (entered by Cntrl-Enter) can be created by CHAR(10). So you have to check by formula what is before and behind the comma space and rewrite into another cell. This answer of SM_Riga could help you.

Syntax examples from German OpenOffice wiki archive:
Code: Select all   Expand viewCollapse view
also possible:

OpenOffice 3.4.1 and 4.1.6 on LinuxMint 19-64 Mate, rarely W7-64 pro and W10-64 pro; LibreOffice current versions
(author is no native speaker)
Posts: 139
Joined: Tue Apr 18, 2017 8:23 am
Location: Germany

Re: Find & Replace regular expressions are not recognized

Postby keme » Sat Jan 12, 2019 1:46 pm

May be a bug in the altsearch plugin. Have you tried to disable it?
Text to columns may provide a workaround. Some work/formula required after running text-to-cols procedure, assuming that you need to get the entries into separate rows of single column again. Probably similar workload as with the formula suggested by erbsenzahl.
User avatar
Posts: 3214
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Find & Replace regular expressions are not recognized

Postby Lupp » Sat Jan 12, 2019 3:56 pm

AltSearch never worked in Calc.
Standard F&R has no means to use hard line breaks in replacement strings.
Since long we have the absurd situation that \n in the 'Search for' is representing a hard line break while the same \n in 'Replace with' represents a paragraph break.
Concerning the insertion of characters via their unicode CodePoint in replacement strings: Otherwise found specifications prefer \uHHHH or \UHHHHHH (3-byte-codepoints) respectively.
Calc cells never accepted paragraph breaks.
(Though the API method, when creating an enumeration of a Calc cell as Text is treating U+000A as if it is a paragraph break.
And though the insertion of Char(13) into a string to return by a formula is accepted, but has no influence on the rendering.
Office software seems to regard consistent inconsistency as its raison d'être.)

To get your desired view use something like =SUBSTITUTE(A2;", ";CHAR(10)) in a spare column.
With 'Copy' and 'Paste Special...', 'Text' only you can later put the results where you want them.

Your example "data1, data2" is suggesting you have something like compound data in a data-keeping spreadsheet application. Compound data are bad independent of whether they are displayed in one line or more. Multiline display may even be a bit worse as it doesn't even clearly show the character(s) used as separator.
Don't continue this way. SPLIT YOUR DATA! Use 'Text to Columns...' for the purpose as already suggested. But keep the split persistent.
Study viewtopic.php?f=9&t=96456 to see deterrent examples.
If you urgently want multiline compounds in "prettyprints" or need them to comply with a prespecified format (like for addresses) you can easily concatenate them. Working with data needing a split first is a nightmare.
Last edited by Lupp on Sat Jan 12, 2019 10:17 pm, edited 2 times in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 2523
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Find & Replace regular expressions are not recognized

Postby TToth » Sat Jan 12, 2019 9:41 pm

OOh. Much thanks for the explanation.
OpenOffice 4.1.6 | Windows 10 Edu x64
Posts: 2
Joined: Fri Jan 11, 2019 4:22 pm

Return to Calc

Who is online

Users browsing this forum: Zizi64 and 12 guests