Appending text to existing text, selectively

[Skompy]

I have a long list (over 15 thousand rows and 5 columns and on 2 sheets) of existing text to which I want to add further text, selectively.
The data is names of racehorses.
I want to append some text (a country code of 2 letters in parenthesis) to the end of each horse's name, preceded by one keyboard space.
So "horsename (XX)" would be the end result, not including the quotes of course.
But some horses already have a country code appended to their name and so I do not want to touch any of those. Those horses that have the country code and those that don't are all mixed up in the same list.
The text that is to be appended will be the same for all horses without a country code already.
So I think I need a way for the program to test if the cell already has the country code/string appended and if not, to add what I specify.

[Zizi64]

Please upload example ODF type files here about the the "Before" and the "After" state of your document.

[Skompy]

File attached

[Zizi64]

I can not see - in your attached file -, where are the texts what you want to add to the names (by formulas or by macros or by other way...)

[keme]

Possible solution attached. See Sheet 2.
Note that the formula is applied to the full range, including table headers. You may want to limit the range...

Note also that this depends on the use of regular expressions, so it will not work with Excel. "Use Regex in formulas" must be enabled.

[Skompy]

I can not see - in your attached file -, where are the texts what you want to add to the names (by formulas or by macros or by other way...)

I think its quite obvious? Its (GB).

[Skompy]

Possible solution attached. See Sheet 2.
Note that the formula is applied to the full range, including table headers. You may want to limit the range...

Note also that this depends on the use of regular expressions, so it will not work with Excel. "Use Regex in formulas" must be enabled.

Thank you. This seems to be a solution.
To do this over a range, do I simply enter the range in the formula, replacing the one cell reference as shown at the moment?

I am unclear what this is about below?

Note also that this depends on the use of regular expressions, so it will not work with Excel. "Use Regex in formulas" must be enabled.

Regular expressions?
Regex?

[keme]

... Thank you. This seems to be a solution.
To do this over a range, do I simply enter the range in the formula, replacing the one cell reference as shown at the moment?

No, you copy the formula, and paste it to the "next sheet" behind your data (create sheets in between if you have several sheets of horse data), over the cell range you want to update with GB nationality. (The same cell addresses which contain horse names in previous sheet).

Note also that this depends on the use of regular expressions, so it will not work with Excel. "Use Regex in formulas" must be enabled.

Regular expressions?
Regex?

"Regular expressions", shorthand "regex", is a way to specify what to search for. Akin to but more advanced than wildcard search. It is enabled in the file I posted, but probably disabled in your file. Find the setting by menu selection Tools - Options - Calc - Calculate.

The regex used:
[:upper:] will match uppercase letters only. {2,3} means that 2 or 3 uppercase letters are required for the expression to match. The backslash before parentheses means "search for parenthesis character verbatim". (Without backslashes the parentheses would retain their regex functionality: grouping withinin the expression.)

[Skompy]

Regular expressions?
Regex? "Regular expressions", shorthand "regex", is a way to specify what to search for. Akin to but more advanced than wildcard search. It is enabled in the file I posted, but probably disabled in your file. Find the setting by menu selection Tools - Options - Calc - Calculate.

I don't have an Options item in my Tools menu option.

[coray80]

My solution is pretty much the same as Keme's but he had already posted before mine was complete and I only post it now as you seem to be having difficulties.
No need to concern yourself with regular expressions
=IF(RIGHT(A4;1)=")";A4;A4&" (GB)")
This formula can be dragged across and down in a separate table (as per the attached)
It checks if the last letter is a closing parenthesis. If it is it will recreate that cell, otherwise it will append " (GB)".
This new table can then be copied to the clipboard and with paste special (formula unchecked) recreated without the formula.

[Skompy]

My solution is pretty much the same as Keme's but he had already posted before mine was complete and I only post it now as you seem to be having difficulties.
No need to concern yourself with regular expressions
=IF(RIGHT(A4;1)=")";A4;A4&" (GB)")
This formula can be dragged across and down in a separate table (as per the attached)
It checks if the last letter is a closing parenthesis. If it is it will recreate that cell, otherwise it will append " (GB)".
This new table can then be copied to the clipboard and with paste special (formula unchecked) recreated without the formula.

This one is good too. Different but gets the same result.
I was hoping that it would be possible to run some sort of command/instruction, like a macro, in which it would just add the (GB) where necessary and that's it. No need for me to deal with separate sheets, copying and pasting thousands of cells, reformatting, etc.
If that is possible, it would be great, but if not, these two options from you and Keme will be fine.

[keme]

A simple find/replace would have to search for cells not containing a country code, which is not a trivial search in a spreadsheet (most likely you'd have to do it in several passes, or possibly by a complicated regex). A macro could be written, but that is also a bit of work. It is probably easier and safer to use a helper sheet with the formula from coray80 or myself, then copy and paste-special (text only, no formulas) back to the original sheet if you want. After copying, the helper sheet can then be removed again.

As long as the horse names will never end with parentheses and there is no trailing space, coray80's formula is just as good. More or less hassle free when you don't need the regular expression, and it will also work in Excel. A trailing space is easy to enter (by accident or by habit), and very hard to spot. Perhaps enhance Coray80's formula slightly:

Code: Select all

=IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)")

The TRIM() function removes leading, trailing and multiple spaces.

[Skompy]

As long as the horse names will never end with parentheses and there is no trailing space, coray80's formula is just as good. More or less hassle free when you don't need the regular expression, and it will also work in Excel. A trailing space is easy to enter (by accident or by habit), and very hard to spot. Perhaps enhance Coray80's formula slightly:

Code: Select all

=IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)")

The TRIM() function removes leading, trailing and multiple spaces.

I copied the above formula and added trailing spaces at the end of 3 horse names. The trailing spaces were not removed.

[keme]

As long as the horse names will never end with parentheses and there is no trailing space, coray80's formula is just as good. More or less hassle free when you don't need the regular expression, and it will also work in Excel. A trailing space is easy to enter (by accident or by habit), and very hard to spot. Perhaps enhance Coray80's formula slightly:

Code: Select all

=IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)")

The TRIM() function removes leading, trailing and multiple spaces.

I copied the above formula and added trailing spaces at the end of 3 horse names. The trailing spaces were not removed.

No, but the formula did detect the closing parenthesis, didn't it? The TRIM() in the above formula is used only in the condition, not in the insertion process. You need to use it once again to do that. This should give the desired formula result:

Code: Select all

=TRIM(IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)"))

Note that the formula will not change the source value, so the trailing spaces you entered will not be touched, but TRIM should stop them from propagating to the formula result.

[Skompy]

Code: Select all

=IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)")

I copied the above formula and added trailing spaces at the end of 3 horse names. The trailing spaces were not removed.

No, but the formula did detect the closing parenthesis, didn't it? The TRIM() in the above formula is used only in the condition, not in the insertion process. You need to use it once again to do that. This should give the desired formula result:

Code: Select all

=TRIM(IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)"))

Note that the formula will not change the source value, so the trailing spaces you entered will not be touched, but TRIM should stop them from propagating to the formula result.

Yes, the previous formula detected the closing parentheses. Your amended formula removed the trailing spaces.
This is a very useful aspect to add to the formula, since many thousands of data elements have been entered, some manual & some copied.
And like you say, its hard, if not impossible to detect where these are in a cell, unless you check each cell!

After I have finished with the (GB) appending part, I suppose I can use a modified version of the formula to trim any empty spaces everywhere else?
Can you tell me what the formula would be for that?
I am curious as to how the formula removes empty spaces before the text string, if you are using the RIGHT variable?

I do apprecaite your help. Thanks!

[Skompy]

Re: =IF(ISTEXT(Sheet1.A3);Sheet1.A3&IF(ISNUMBER(SEARCH("\([:upper:]{2,3}\)";Sheet1.A3));"";" (GB)");"")

I have tried to use this formula, finally, to my data and the result is that it is appending (GB) to all horse names, not just to the ones without country codes.
I changed the cell reference (A3) to match the first cell in my data range (L9) but that is all.

Anyone suggest what I need to do to fix this?