[Solved] Find&Replace regular expressions are not recognized

Discuss the spreadsheet application
Post Reply
TToth
Posts: 2
Joined: Fri Jan 11, 2019 4:22 pm

[Solved] Find&Replace regular expressions are not recognized

Post by TToth »

[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
data1
data2
But when a try to replace the ", " with "\n" i get:
  • Reqs
data1\ndata2
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.

Tried:
  • [\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
erbsenzahl
Volunteer
Posts: 254
Joined: Tue Apr 18, 2017 8:23 am
Location: Germany

Re: Find & Replace regular expressions are not recognized

Post by erbsenzahl »

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

=A1&":"&CHAR(10)&B1
also possible:
=CONCATENATE(A1;CHAR(10);B1)
Godspeed
LibreOffice current versions 7 and OpenOffice 4.1.15
on LinuxMint 20 - 21 Mate, W10-64 pro
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Find & Replace regular expressions are not recognized

Post by keme »

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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Find & Replace regular expressions are not recognized

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
TToth
Posts: 2
Joined: Fri Jan 11, 2019 4:22 pm

Re: Find & Replace regular expressions are not recognized

Post by TToth »

OOh. Much thanks for the explanation.
OpenOffice 4.1.6 | Windows 10 Edu x64
Post Reply