Can I replace some word to newLine with search and replace dialog?

English
#1

Hi, I use calc 7.3.6.2.
I want to replace a word (e.g xxxxx) to newLine with search and replace dialog.

In the dialog,
I can replace newline to some word.
\n means newLine,
then I type \n to SearchBox and type xxxx to ReplaceBox,
and I made RegularExpression check box checked,
then I press ReplaceAll button.

Now, I want to replace xxxxx to newLine.
then I type xxxxx to SearchBox and type \n to ReplaceBox,
then press ReplaceAll button.
But I can’t replace.
NewLine is not displaied,but ‘\n’ is displaied.

Substitute function can replace xxxxx to NewLine.

But I want to do it,with ‘search and replace dialog’.

How can I do it?

Sincerely

#2

If you are replacing within quoted strings (not just cell text content), then a hack is to turn off regular expressions and replace the given word (depending, possibly with spaces padding both sides) with

" & Char(10) & "

So in a cell with
.
=“a hello b”
.
this would become
.
=“a” & Char(10) & “b”
.
which would have the desired effect. Undoing this would then require replacing the "& Char(10) & " back with some word, of course.

[Edited for clarity, since there is still no general solution this may still have value in niche cases.]

#3

Thank you ! @joshua4

According to your reply,I tried to it.
I turned off regular expression,then I replaced.
but

a hello b
↓(replaced to)
a & Char(10) & b

I tried some patterns
like
SearchBox ‘hello’

ReplaceBox
& Char(10) &
" & Char(10) & "
“Char(10)”
Char(10)

results are
a & Char(10) & b
a " & Char(10) & " b
a Char(10) b
a “Char(10)” b

I want to replace hello to newLine
like
a hello b
↓
a
b

Maybe I could not understand the difference
quoted strings with cell text content.

What is wrong with my operation?

#4

The current behavior of RegEx replacement in Calc is strange (inconsistent). Resorting to user code we can replace findings with LF, but for reasons I can’t see, optimal height for the afflicted rows doesn’t work.
For a single cell range there is a funny workaround for which you may need a few minutes the first time. After some training it should be faster than editing every single cell, however.

  1. Copy the range
  2. Create an empty text document as helper.
  3. PasteSpecial the clipboard content therein as RichTextFormat,
  4. Do the needed F&R with RegEx and \n in the replacement string.
  5. Copy the result again
  6. PasteSpecial it (RTF again) To the source range in your spreadsheet.

Funny enough? It actually works!

#5

Thank you @Lupp !

I could replace!

You mean that ‘Calc can not replace some word to newLine,but Writer can replace some word to newLine’?

But why Calc can’t replace it ?
Is this bug?

Must I report this to BugZilla?

sincerely,

#6

What do you mean?

#7

@erAck I tend to agree with @Lupp, because, since the newlines inserted into the cells create separate paragraphs:

<table:table-cell office:value-type="string"><text:p>a</text:p><text:p>b</text:p></table:table-cell>

then it would be natural that \n in Calc’s replacement dialog also inserted that paragraph, as it does in Writer (tdf#43107).

Of course, there’s also tdf#106137.

#8

Unfortunartely it’s much more complicated, and there are many details I also don’t know. In addition there were case where I “researched” into the details, and had to accept that some were changed later without notice. There are bug reports and enhancement requests concerning the field (some reported by myself). A very old one concerning RegEx in Calc and also the special control \n is tdf# 43107. The actual (7.4.1) behavior is even worse in a detail: The young REGEX() function doesn’t replace with \n, but with n, and by that cover the tracks. The mentioned bug, however, never was assigned or marked “RESOLVED”.
“Why can’t Calc do something, Writer easily can?”
Well, neither Calc nor Writer do the labor. Concerning the handling of Unicode LibO generally doesn’t work with routines of their own development, but use tools provided by ICU. The different and partly erroneous behavior depending on the component class and other aspects, however, is actually LibO-made.

This was a kind of challenge, and I therefore didn’t only invent a workaround using the old Feynman trick, but also wrote code (accompanied by some tools from my Basic-box)

  • to give a possible solution for those ready to resort to user code
  • to demonstrate the rather deep cause of the issue - as I see it.

See this attachment: replaceRegExFindingsWithLineFeeds.ods (22.2 KB)

My best advice: Omit Line breaks in spreadsheet cells.

#9

While I wrote my comment full of “guessed wisdom” two developers actually in the know posted above.
However, I try another guess:
ranges.replaceAll(ReplaceDescriptor) will work with DataArray objects. This directly assigns the strings without calling a method to rework the (enumerable) text and to recalculate the optimal row-heights. Therefore inserted (supposed) control characters don’t make sense.
You can easily reduce my “Fat” routine of the example to a version not working with every single cell. A probable Chr(10) or whatever of the kind will be inserted by the RegEx engine, but will not cause the intended effects concerning the wrapping.
[[]Anyway: The current REGEX() behavior concerning \n is a bug. “Current” = V 7.4.1.2.
What I mean is, that for the replacement string “\n” only the “n” - without the backslash is inserted.]]

#10

Hallo
quick & dirty: replace in selection " xxx " with linefeed

def replace_by_linefeed(*_):
    doc=XSCRIPTCONTEXT.getDocument()
    selection = doc.CurrentSelection
    search = selection.createSearchDescriptor()
    search.SearchString = " xxx "
    found_all = selection.findAll(search)
    undo = doc.UndoManager
    undo.enterUndoContext("replace")
    for found in found_all:
        data = found.DataArray
        out = []
        for row in data:
            out.append(["\n".join(entry.split(" xxx ")) for entry in row])
        found.FormulaArray = out
        found.Rows.OptimalHeight = True
    undo.leaveUndoContext()
#11

Hmmm…
I would consider current behavior of REGEX OK, and current behavior of F&R dialog questionable WRT the backslash, given how the ICU engine defines the replacement string. Ideally, I’d treat \n as “insert new paragraph” in Calc, too; and also the other extension that you suggested in tdf# 106137; but allowed the backslash to serve as escaping character otherwise.

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