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...)
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.
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.
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?
"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.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?
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.
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.
=IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)")
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.
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.
=TRIM(IF(RIGHT(TRIM(A4);1)=")";A4;A4&" (GB)"))
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.
Users browsing this forum: No registered users and 22 guests