Appending text to existing text, selectively

Discuss the spreadsheet application

Appending text to existing text, selectively

Postby Skompy » Wed Mar 29, 2017 5:05 pm

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.
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: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: Appending text to existing text, selectively

Postby Zizi64 » Wed Mar 29, 2017 5:47 pm

Please upload example ODF type files here about the the "Before" and the "After" state of your document.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8546
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Appending text to existing text, selectively

Postby Skompy » Wed Mar 29, 2017 6:01 pm

File attached
Attachments
Horse names sample 1.ods
(24.56 KiB) Downloaded 42 times
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: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: Appending text to existing text, selectively

Postby Zizi64 » Wed Mar 29, 2017 6:25 pm

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...)
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8546
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Appending text to existing text, selectively

Postby keme » Wed Mar 29, 2017 6:33 pm

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.
Attachments
Horse names.ods
(26.43 KiB) Downloaded 45 times
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3286
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Appending text to existing text, selectively

Postby Skompy » Wed Mar 29, 2017 6:42 pm

Zizi64 wrote: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).
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: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: Appending text to existing text, selectively

Postby Skompy » Wed Mar 29, 2017 6:46 pm

keme wrote: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?

keme wrote: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?
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: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: Appending text to existing text, selectively

Postby keme » Wed Mar 29, 2017 7:38 pm

Skompy wrote:... 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).
Skompy wrote:
keme wrote: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.)
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3286
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Appending text to existing text, selectively

Postby Skompy » Thu Mar 30, 2017 4:59 am

keme wrote: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.
Last edited by Skompy on Thu Mar 30, 2017 2:23 pm, edited 1 time in total.
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: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: Appending text to existing text, selectively

Postby coray80 » Thu Mar 30, 2017 5:45 am

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.
Attachments
skompy.ods
(18.52 KiB) Downloaded 38 times
OpenOffice 4.1.2 on Windows 7
If your question has been answered please add [solved] to the title by using the edit button at your first post
coray80
Volunteer
 
Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

Re: Appending text to existing text, selectively

Postby Skompy » Thu Mar 30, 2017 2:30 pm

coray80 wrote: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.
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: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: Appending text to existing text, selectively

Postby keme » Thu Mar 30, 2017 3:43 pm

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   Expand viewCollapse view
=IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)")

The TRIM() function removes leading, trailing and multiple spaces.
User avatar
keme
Volunteer
 
Posts: 3286
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Appending text to existing text, selectively

Postby Skompy » Thu Mar 30, 2017 3:49 pm

keme wrote: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   Expand viewCollapse view
=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.
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: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: Appending text to existing text, selectively

Postby keme » Thu Mar 30, 2017 3:57 pm

Skompy wrote:
keme wrote: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   Expand viewCollapse view
=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   Expand viewCollapse view
=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.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3286
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Appending text to existing text, selectively

Postby Skompy » Thu Mar 30, 2017 4:11 pm

keme wrote:
Code: Select all   Expand viewCollapse view
=IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)")

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


keme wrote: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   Expand viewCollapse view
=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!
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: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: Appending text to existing text, selectively

Postby Skompy » Tue Apr 30, 2019 7:22 pm

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?
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: 98
Joined: Tue Feb 18, 2014 10:35 am


Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 32 guests