[Solved] Regular Expressions in search/replace

Discuss the spreadsheet application

[Solved] Regular Expressions in search/replace

Postby amgbg » Thu Aug 10, 2017 4:41 pm

I have hundereds of rows in Calc with the pattern: XX:CH P54, YY:CH J60, ZZ:CH T67, WW:CH P54 (numbers differ for each item).

I want to delete everything but "XX:CH P54".

Have tried "Regular Expressions" in search/replace without success.
Last edited by RoryOF on Sun Aug 13, 2017 7:18 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
Apache OpenOffice 4.1
Windows 8
amgbg
 
Posts: 10
Joined: Tue Jul 01, 2014 5:01 pm

Re: Regular Expressions in search/replace

Postby acknak » Thu Aug 10, 2017 6:04 pm

Maybe someone else has an idea, but I think this may require multiple passes: first to mark the items you want; then to match un-marked items, and finally to remove the marks.

For example,

1) Find/Replace XX:CH P54 with @XX:CH P54

2) Find all: ^[^@]
(Replace with (empty), or press delete)

3) Find/Replace ^@(.*) with $1

If you need details, just ask.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22467
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Regular Expressions in search/replace

Postby Lupp » Thu Aug 10, 2017 7:19 pm

@acknak:
I am once more baffled that you can try to answer a question I do not understand even basically.

OK. The "patterns" should be described by the regular expression
Code: Select all   Expand viewCollapse view
([A-Z])\1:[A-Z]{2} [A-Z][1-9][0-9]
Or probably [0-9]+ for the "numeric" part? Or something else?

But: Is anyone of the "items" ONE specimen matching the pattern? Or a list ?
If one per row: This is a question of filtering.
If a list per row: Are there assurances about the occurrence of the specific one "XX:CH P54"?
Where are a few examplesof "This I have" and "This I want to get for it"?
(What's the secret behind doubled letters?)

Please help me understand.

If I take the question as literal as I can, the task would require to search for
Code: Select all   Expand viewCollapse view
^(.*)(XX:CH P54)(.*)$
and to replace with
Code: Select all   Expand viewCollapse view
$2
Regular expressions in 'F&R' must be enabled, of course.
On Windows 10: LibreOffice 5.4.0 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1358
Joined: Sat May 31, 2014 7:05 pm

Re: Regular Expressions in search/replace

Postby acknak » Thu Aug 10, 2017 9:35 pm

Lupp wrote:@acknak:
I am once more baffled that you can try to answer a question I do not understand even basically.
...

Probably not a good thing: I'm not sure I understand it that well either.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22467
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Regular Expressions in search/replace

Postby Alex1 » Fri Aug 11, 2017 1:09 am

If you want to keep the first occurrence of ":CH P54", you might try this:
Search for: ^(XX:CH P\d\d).*$
Replace with: $1
Under More Options, check Regular expressions, and current selection only if applicable.
AOO 4.1.3 & LO 5.0.6 on Windows XP & 7
Alex1
Volunteer
 
Posts: 586
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: Regular Expressions in search/replace

Postby John_Ha » Fri Aug 11, 2017 2:37 pm

Can you upload a small spreadsheet with 3 or 4 rows so that we can be sure we know what we are dealing with. Use the Upload attachment tab below where you type [on the Post Reply or Full Editor screens].
Last edited by RoryOF on Fri Aug 11, 2017 2:40 pm, edited 1 time in total.
Reason: Added explanatory words [RoryOF, Moderator]
AOO 4.1.3, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the up to date Writer guide for information. Click the Help button on a pop-up window for extensive help on that function.
John_Ha
Volunteer
 
Posts: 4150
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Regular Expressions in search/replace

Postby amgbg » Sat Aug 12, 2017 6:25 pm

I thought this would have been easily solved. Maybe my first expalation was not good enough.

I uplad the entire file regular expression list.ods

The problem: I want to keep only the item text XX:CH 370, but CH 370 will differ between every item. There is not even always 3 numbers following CH, it could even be a mixture of numbers and letters. So the entire text following XX: must be replaced by regular expressions.

I thought
Search for: ^(XX:CH P\d\d).*$
Replace with: $1
would be the solution, but it gives "Search key not found" as well as my previous tries.
Attachments
regular expression list.ods
(16.91 KiB) Downloaded 11 times
Apache OpenOffice 4.1
Windows 8
amgbg
 
Posts: 10
Joined: Tue Jul 01, 2014 5:01 pm

Re: Regular Expressions in search/replace

Postby acknak » Sat Aug 12, 2017 8:16 pm

Ok, so you want to match all the entries matching the pattern and get rid of everything else, no?

OO Calc is not at all convenient for such a task, especially for the general case where the matched item can appear at any position or with multiple matches in the same line.

Simple approach: save your data as a text file and use grep (a command-line regular expression filter):
    $ grep -o 'XX:CH P[A-Z0-9]*' 'regular expression list.txt'
    XX:CH P38
    XX:CH P42
    XX:CH P44
    XX:CH P47
    XX:CH P54
    XX:CH P57
    XX:CH PF15I
The "-o" option means "print ONLY the matched text".

In Calc, there's no way to work with or extract only the matched text. You always have to make the pattern match the part you DON'T want, and replace that with nothing to delete it. If the strings you're looking for can appear anywhere in the line, or if they can appear more than once in the same line, I'm not sure I know how to manage that in Calc. At least I don't see any easy way.

I'm not quite sure why your pattern is failing; I have to play with it a bit.

 Edit: PS: 
Your pattern/replace all works for me with your file—that is, the pattern/replace works as it should; it just doesn't do what (I understand) you want.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22467
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Regular Expressions in search/replace

Postby keme » Sat Aug 12, 2017 10:31 pm

Perhaps we are trying to make this problem harder than it is.

If the XX:... code is always the first in the string (which it is in the sample file), and entries are always delimited by comma, you can simply delete anything from the first comma.

Search for ,.* (comma period asterisk)
Replace with nothing.
User avatar
keme
Volunteer
 
Posts: 2550
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Regular Expressions in search/replace

Postby Lupp » Sat Aug 12, 2017 10:32 pm

The attachment demonstrates a few approaches.
The most powerful of them depends on a split function which is (basically) implemented in AOO BASIC, but not available for Calc.
I made a few general-pupose user-functions to make a Split and also a TextJoin available in Calc. The tow columns made to demonstrate solutions based on (at least) one of these functions will only work if "macros" are permitted.
Attachments
aoo89906SpecialExtract_1.ods
(44.58 KiB) Downloaded 4 times
On Windows 10: LibreOffice 5.4.0 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1358
Joined: Sat May 31, 2014 7:05 pm

Re: Regular Expressions in search/replace

Postby Lupp » Sat Aug 12, 2017 10:34 pm

keme wrote:Perhaps we are trying to make this problem harder than it is.

If the XX:... code is always the first in the string (which it is in the sample file), and entries are always delimited by comma, you can simply delete anything from the first comma.

Search for ,.* (comma period asterisk)
Replace with nothing.

Yes. My first solution (post above) makes this assumption.
However, I would love clear and explicit assurances.
On Windows 10: LibreOffice 5.4.0 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1358
Joined: Sat May 31, 2014 7:05 pm

Re: Regular Expressions in search/replace

Postby amgbg » Sun Aug 13, 2017 5:58 pm

Thank you for your help!

At the end it was really easy:

Search for ,.* (comma period asterisk)
Replace with nothing.

Works perfectly!
Apache OpenOffice 4.1
Windows 8
amgbg
 
Posts: 10
Joined: Tue Jul 01, 2014 5:01 pm


Return to Calc

Who is online

Users browsing this forum: hubert lambert and 49 guests