[Solved] Regular Expression for empy cells

Discuss the spreadsheet application
Post Reply
augus1990
Posts: 2
Joined: Fri Jul 01, 2016 5:41 am

[Solved] Regular Expression for empy cells

Post 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).
Last edited by Hagar Delest on Sat Jul 02, 2016 3:30 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4 / Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

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

Post 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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Bill
Volunteer
Posts: 8932
Joined: Sat Nov 24, 2007 6:48 am

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

Post 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.
AOO 4.1.14 on Ubuntu MATE 22.04
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

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

Post by John_Ha »

Bill

Thanks. I wish people would start new threads!
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
augus1990
Posts: 2
Joined: Fri Jul 01, 2016 5:41 am

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

Post 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/Docume ... nd_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!
OpenOffice 4 / Windows 10
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Post 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.
AOO4/LO5 • Linux • Fedora 23
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

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

Post 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
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

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

Post 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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Regular Expression for empy cells

Post by Lupp »

... and a roughly sketched emergency-workaround by user code in Basic:

Code: Select all

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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: [Solved] Regular Expression for empy cells

Post by eremmel »

Lupp, thanks. Good to explore this route.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply