[Solved] Search & replace quandary

Discuss the spreadsheet application
Post Reply
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

[Solved] Search & replace quandary

Post by Skompy »

I made a mistake with a formula and now I have an unknown number of cells with a closing parenthesis missing.
What I'd like to be able to do is run the search & replace function to append the missing parenthesis on all the affected cells.
There could be thousands of cells affected.

The affected cells all end with the string "(GB" (no quotes. There is also a space before the G but I don't know if that is important or not. I don't have any cells without a space before the opening parenthesis. So what I want to end up with is: (GB).

I have tried to use the wildcard character .* but this hasn't worked. Maybe I'm not doing it right.
Last edited by Hagar Delest on Mon May 06, 2019 8:52 pm, edited 1 time in total.
Reason: tagged solved
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Search & replace quandary

Post by RoryOF »

Tell OO to find GB, match case,and Replace with &). Drop More Options, select Regular Expressions. Replace All.

Then, lest there be any GB)), Find GB)) match case and Replace GB), unselect Regular expressions. Replace all.

Best to do this on a copy of the file just in case something goes wrong.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Search & replace quandary

Post by Skompy »

Your suggestion seems to have worked, although at first I was concerned as it was changing all cells with (GB) to (GB)) too.

Is there a way I can test to see if any cells still have (GB ?
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search & replace quandary

Post by Lupp »

Everybody having to use RegEx should read (at least) the help texts.
For additional reading and consulting I would recommend
https://www.regular-expressions.info/ and
http://userguide.icu-project.org/strings/regexp (because the ICU engine is what's used by AOO and also by LibO).

Anyway you need to know that the opening parenthese is a special character in RegEx search - and if present an opening one also the closing parenthese pairing with it. Pairs of parentheses are used to define groups which can be referred to then with the currently accepted value.

If you want to have a literal parenthese you need to "escape" its special role by a prefixed backslah "\".
This does not apply to the replace string.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Search & replace quandary

Post by Skompy »

Lupp wrote:Everybody having to use RegEx should read (at least) the help texts.
For additional reading and consulting I would recommend
https://www.regular-expressions.info/ and
http://userguide.icu-project.org/strings/regexp (because the ICU engine is what's used by AOO and also by LibO).

Anyway you need to know that the opening parenthese is a special character in RegEx search - and if present an opening one also the closing parenthese pairing with it. Pairs of parentheses are used to define groups which can be referred to then with the currently accepted value.

If you want to have a literal parenthese you need to "escape" its special role by a prefixed backslah "\".
This does not apply to the replace string.
Thanks but I am completely befuddled by "trying" to read the above and I have no idea what they are talking about.
I am not technical person and often find abstract things very difficult. Additionally, historically I have not been able to understand/learn more than simple things from reading words alone. I usually need a person to learn from and I always have questions.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Search & replace quandary

Post by RoryOF »

While I can and have used Regular expressions, I am a great believer in simplicity and almost always prefer to break my F&R operation into a number of separate operations - a number of F&R operations can be written and executed by the time one has checked the Regular expression syntax and verified the formula one needs to use.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Search & replace quandary

Post by Skompy »

RoryOF wrote:While I can and have used Regular expressions, I am a great believer in simplicity and almost always prefer to break my F&R operation into a number of separate operations - a number of F&R operations can be written and executed by the time one has checked the Regular expression syntax and verified the formula one needs to use.
Can you help with a wildcard character, so I can check for any cells that have (GB?
All cells have text before the space and (GB).
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search & replace quandary

Post by Lupp »

To serch for a literal "GB" probably preceded by one space and everything surely prefixed by an opening parenthese you need to use "\( ?GB" as the RegEx to search for. There the "\(" acts as a literal "(" and the " ?" for exactly one space or none at all.

You may want to replace this simply by "(GB)", this way closing the pair of parentheses and at the same time getting rid of the superfluous space (if any). (If you want to keep the optional space, please ask again.)

However, if there also can occur already complete "(GB)" the above proceding would result in a doubled closing parenthese "(GB))". To avoid this you would have to use a slightly more complicated RegEx. The concept of "wildcards" as used in MS software is by far too primitive (weak) to help this far.

Please attach a relevant and sufficiently rich collection of examples as an .odt or a .ods file here.

See attached example.
aoo97916SpecialFandR_1.ods
(63.49 KiB) Downloaded 136 times
This can, of course, only work if the texts to procede ar constant texts, not formula results.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Search & replace quandary

Post by Skompy »

Lupp wrote:To serch for a literal "GB" probably preceded by one space and everything surely prefixed by an opening parenthese you need to use "\( ?GB" as the RegEx to search for. There the "\(" acts as a literal "(" and the " ?" for exactly one space or none at all.

You may want to replace this simply by "(GB)", this way closing the pair of parentheses and at the same time getting rid of the superfluous space (if any). (If you want to keep the optional space, please ask again.)

However, if there also can occur already complete "(GB)" the above proceding would result in a doubled closing parenthese "(GB))". To avoid this you would have to use a slightly more complicated RegEx. The concept of "wildcards" as used in MS software is by far too primitive (weak) to help this far.

Please attach a relevant and sufficiently rich collection of examples as an .odt or a .ods file here.

See attached example.
aoo97916SpecialFandR_1.ods
This can, of course, only work if the texts to procede ar constant texts, not formula results.

Perhaps I have not been clear enough so I shall try again.

I have a list of horse names. After the end of each horse's name, there is a space. Then there is an open parenthesis, then a country code of 2 or 3 letters and then a closing parenthesis. There are no spaces between the open parenthesis, the country code and the closing parenthesis.

The only horse names I want to work on are those with a country code of (GB) appended to their name.
So, example: Flying Star (GB)

Because of the result of a previous and incorrect formula, some of these horse names have the closing parenthesis missing -
e.g. Flying Star (GB

All I wanted to do is add the closing parenthesis, but only for those horse names that have it missing.

RoryOF provided the solution and is seems to have worked. But I just want to check that none of these names have (GB left at the end, instead of (GB). This is all I need now.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Search & replace quandary

Post by RoryOF »

If you did as I suggest, all entries that matched the given Find pattern will be changed to the pattern in Replace. If there are other entries that have a slightly different pattern, say with extra spaces, modify the Find pattern, then change the Replace pattern to be more exact, such as (GB).

The only thing one needs to check with Find and Replace are if the first and last items are at the exact start or finish of the file - these can sometimes not be replaced.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search & replace quandary

Post by Lupp »

aoo97916SpecialFandR_2.ods
(38.85 KiB) Downloaded 112 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
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Search & replace quandary

Post by Skompy »

Lupp wrote:
aoo97916SpecialFandR_2.ods

Thank you, even though I don't understand it.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Search & replace quandary

Post by Skompy »

Lupp wrote:
aoo97916SpecialFandR_2.ods
I just found, by accident, that 3 horse names which had (IRE) at the end, were appended with (GB).
So those horse's names ended up as horsename (IRE) (GB).
Since I don't think I would have done this manually, I can only assume the above S&R operation did that.

I have just manually changed them as there were so few. If I do find any other iterations of this error, I'll let you know.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
Post Reply