[Solved] Regular Expression for empy cells

Discuss the spreadsheet application

[Solved] Regular Expression for empy cells

Postby augus1990 » Fri Jul 01, 2016 5:50 am

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
augus1990
 
Posts: 2
Joined: Fri Jul 01, 2016 5:41 am

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

Postby John_Ha » Fri Jul 01, 2016 3:43 pm

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.
AOO 4.1.6, Windows 7 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.
John_Ha
Volunteer
 
Posts: 6830
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

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

Postby FJCC » Fri Jul 01, 2016 4:08 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7315
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby Bill » Fri Jul 01, 2016 4:25 pm

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.6 and LO 6.2.0.3 on Manjaro MATE
Bill
Volunteer
 
Posts: 7376
Joined: Sat Nov 24, 2007 6:48 am

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

Postby John_Ha » Fri Jul 01, 2016 6:27 pm

Bill

Thanks. I wish people would start new threads!
AOO 4.1.6, Windows 7 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.
John_Ha
Volunteer
 
Posts: 6830
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

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

Postby augus1990 » Sat Jul 02, 2016 6:18 am

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!
OpenOffice 4 / Windows 10
augus1990
 
Posts: 2
Joined: Fri Jul 01, 2016 5:41 am

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

Postby acknak » Sat Jul 02, 2016 9:51 am

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
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

Postby John_Ha » Sat Jul 02, 2016 11:23 am

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

The Calc forum is here
AOO 4.1.6, Windows 7 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.
John_Ha
Volunteer
 
Posts: 6830
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

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

Postby eremmel » Tue Dec 11, 2018 10:51 am

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: [Solved] Regular Expression for empy cells

Postby Lupp » Tue Dec 11, 2018 1:07 pm

... 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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Regular Expression for empy cells

Postby eremmel » Tue Dec 11, 2018 5:01 pm

Lupp, thanks. Good to explore this route.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am


Return to Calc

Who is online

Users browsing this forum: RPG and 24 guests