[Solved] Search & replace quandary

[Hagar Delest]

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.

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

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

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

[Skompy]

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.

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

[Skompy]

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

[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 11 times

This can, of course, only work if the texts to procede ar constant texts, not formula results.

[Skompy]

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.

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

[Lupp]

aoo97916SpecialFandR_2.ods

(38.85 KiB) Downloaded 13 times

[Skompy]

aoo97916SpecialFandR_2.ods

Thank you, even though I don't understand it.

[Skompy]

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.