Page 1 of 1

[Solved] Regular Expression for empy cells

PostPosted: Fri Jul 01, 2016 5:50 am
by augus1990
I have the same problem. Openoffice implements ^ and $ characters incorrectly for regex. How we could add a word at the beggining of each cell? For example in the text editor Notepad++ we activate regex and replace ^ (beginning of line) for the word we want to add at the begining of the line.

I'm using OpenOffice 4. It's just a bug in the regex implementation. Please fix it.

Topic split and moved to the Calc forum. (Hagar, Moderator).

Re: Regular Expression beginning of paragraph (^) doesn't wo

PostPosted: Fri Jul 01, 2016 3:43 pm
by John_Ha
AOO 3.1.0 (for Windows) is completely obsolete - we are now on 4.1.2. Are you on the latest release for LInux?

The Alternative Search add-on may work better because some of the add-on is implemented directly in code rather than in the macro language.

See Documentation > How Tos > Regular Expressions in Writer. Jeffrey Friedl's book Mastering Regular Expressions is excellent.

See [Tutorial] Differences between Writer and MS Word files for useful information.

If this solves the problem, please click the Edit button on your original post and add [Solved] in front of your subject.

Re: Regular Expression beginning of paragraph (^) doesn't wo

PostPosted: Fri Jul 01, 2016 4:08 pm
by FJCC
To add a new word at the beginning of each cell in a table in writer or cells in Calc, I can search for
^.
and replace with
MyNewWord &
I assumed there is only one paragraph within the cells. That is the expected behavior. As acknak says above, the ^ does not by itself match the beginning of a paragraph, it means that the following expression must be at the beginning of a paragraph. You may be accustomed to different behavior but the detailed implementation of regular expressions does vary in different software.

Re: Regular Expression beginning of paragraph (^) doesn't wo

PostPosted: Fri Jul 01, 2016 4:25 pm
by Bill
John_Ha wrote:AOO 3.1.0 (for Windows) is completely obsolete - we are now on 4.1.2. Are you on the latest release for LInux?

This is an ancient thread. The OP was using that version 7 years ago.

Re: Regular Expression beginning of paragraph (^) doesn't wo

PostPosted: Fri Jul 01, 2016 6:27 pm
by John_Ha
Bill

Thanks. I wish people would start new threads!

Re: Regular Expression beginning of paragraph (^) doesn't wo

PostPosted: Sat Jul 02, 2016 6:18 am
by augus1990
acknak wrote:It works for me. ^. finds the first character of every paragraph; ^$ finds empty paragraphs.

You can't use ^ all alone to find the beginning of paragraphs; it only "anchors" the rest of the pattern to match at the beginning of a paragraph.

What is the pattern you're searching for?


I found that ^$ doesn't work because finding empty cell is explicitly disabled in OpenOffice even if you are only searching a selection. You can read about that here in the section "Regular expressions in Calc Find & Replace":

https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Find_and_replace

So it's sadly impossible to find or replace empty cells in OpenOffice with regular expressions.

FJCC wrote:To add a new word at the beginning of each cell in a table in writer or cells in Calc, I can search for
^.
and replace with
MyNewWord &
I assumed there is only one paragraph within the cells. That is the expected behavior. As acknak says above, the ^ does not by itself match the beginning of a paragraph, it means that the following expression must be at the beginning of a paragraph. You may be accustomed to different behavior but the detailed implementation of regular expressions does vary in different software.


That worked! and I found another possible solution too using regex groups:

Search for:
^(.)
and Replace with:
MyNewWord$1

Thank you, bye!

Re: Regular Expression beginning of paragraph (^) doesn't wo

PostPosted: Sat Jul 02, 2016 9:51 am
by acknak
augus1990 wrote:... So it's sadly impossible to find or replace empty cells in OpenOffice with regular expressions. ...

Please note that OO Calc and OO Writer are different in this respect. This topic and my comment were directed at someone using OO Writer; the result will be different if you're working in Calc.

Empty cells in a spreadsheet are completely empty; there's nothing to search so they never match any regular expression.

Re: Regular Expression beginning of paragraph (^) doesn't wo

PostPosted: Sat Jul 02, 2016 11:23 am
by John_Ha
augus1990 wrote:... So it's sadly impossible to find or replace empty cells in OpenOffice Calc with regular expressions. ...

The Calc forum is here

Re: Regular Expression beginning of paragraph (^) doesn't wo

PostPosted: Tue Dec 11, 2018 10:51 am
by eremmel
acknak wrote:
augus1990 wrote:... So it's sadly impossible to find or replace empty cells in OpenOffice with regular expressions. ...

Please note that OO Calc and OO Writer are different in this respect. This topic and my comment were directed at someone using OO Writer; the result will be different if you're working in Calc.

Empty cells in a spreadsheet are completely empty; there's nothing to search so they never match any regular expression.


Just an update as of Dec 2018:

When I assign the formula ="a" and search for ^a$ on cell values I get a match, but when I change forumula to ="" and search for ^$ I do not get a match. This as a result of not able to find empty cells (Tried AOO 4.1.4 and LO 6.0.5.2). Empty Cell search is possible in Excel by leaving the Find-input empty.

Re: [Solved] Regular Expression for empy cells

PostPosted: Tue Dec 11, 2018 1:07 pm
by Lupp
... and a roughly sketched emergency-workaround by user code in Basic:
Code: Select all   Expand viewCollapse view
Sub fillEmptyPartOfCurrentSelectionWith(Optional pNewString As String)
If IsMissing(pNewString) Then pNewString = "<found empty>"
doc0             = ThisComponent
rgs              = doc0.CurrentSelection
e                = rgs.queryEmptyCells
For Each rgA In e.RangeAddresses
  With rgA
    rg               = doc0.Sheets(.Sheet).getCellRangeByPosition _
                          (.StartColumn, .StartRow, .EndColumn, .EndRow)
  End With
  startCell        = rg.getCellByPosition(0, 0)
  startCell.String = pNewString
  rg.FillAuto(0, 1)
  rg.FillAuto(1, 1)
Next rgA
End Sub
This will not touch cells with empty formula result.

Re: [Solved] Regular Expression for empy cells

PostPosted: Tue Dec 11, 2018 5:01 pm
by eremmel
Lupp, thanks. Good to explore this route.