[Solved] Regular Expressions in search/replace
[Solved] Regular Expressions in search/replace
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.
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]
Reason: Added green tick [RoryOF, Moderator]
Apache OpenOffice 4.1
Windows 8
Windows 8
Re: Regular Expressions in search/replace
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.
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
Re: Regular Expressions in search/replace
@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 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 forand to replace with Regular expressions in 'F&R' must be enabled, of course.
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
([A-Z])\1:[A-Z]{2} [A-Z][1-9][0-9]
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
^(.*)(XX:CH P54)(.*)$
Code: Select all
$2
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Regular Expressions in search/replace
Probably not a good thing: I'm not sure I understand it that well either.Lupp wrote:@acknak:
I am once more baffled that you can try to answer a question I do not understand even basically.
...
AOO4/LO5 • Linux • Fedora 23
Re: Regular Expressions in search/replace
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.
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.15 & LO 24.2.2 on Windows 10
Re: Regular Expressions in search/replace
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]
Reason: Added explanatory words [RoryOF, Moderator]
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.
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.
Re: Regular Expressions in search/replace
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.
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 223 times
Apache OpenOffice 4.1
Windows 8
Windows 8
Re: Regular Expressions in search/replace
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):
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.
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.
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
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: |
AOO4/LO5 • Linux • Fedora 23
Re: Regular Expressions in search/replace
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.
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.
Re: Regular Expressions in search/replace
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.
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 177 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Regular Expressions in search/replace
Yes. My first solution (post above) makes this assumption.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.
However, I would love clear and explicit assurances.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Regular Expressions in search/replace
Thank you for your help!
At the end it was really easy:
Search for ,.* (comma period asterisk)
Replace with nothing.
Works perfectly!
At the end it was really easy:
Search for ,.* (comma period asterisk)
Replace with nothing.
Works perfectly!
Apache OpenOffice 4.1
Windows 8
Windows 8