Other that use find and replace to remove hyphens in a sheet is there
another way to remove hyphens in only certain parts of a string. Here are
some examples of what i want to do:


before
Acalypha hispida 'Alba'
after
Acalypha hispida Alba

before
Acanthus mollis 'Hollard's Gold'
after
Acanthus mollis Hollard's Gold

before
'Acer campestre Postelense'
after
Acer campestre Postelense

As you can see there are several options. Can a formula take all these
criteria's into account, particularly the second example where a hyphen
needs to be retained.

Pat

Hi Pat,

Seems difficult; we need more info on the rules.

For example, in:

Acanthus mollis 'Hollard's sister's Gold'

Which apostrophe do you think ought to be removed and why?

--
Kind regards,

Niek Otten


"Pat" wrote in message
...
Other that use find and replace to remove hyphens in a sheet is there
another way to remove hyphens in only certain parts of a string. Here are
some examples of what i want to do:


before
Acalypha hispida 'Alba'
after
Acalypha hispida Alba

before
Acanthus mollis 'Hollard's Gold'
after
Acanthus mollis Hollard's Gold

before
'Acer campestre Postelense'
after
Acer campestre Postelense

As you can see there are several options. Can a formula take all these
criteria's into account, particularly the second example where a hyphen
needs to be retained.

Pat

I thought it would be challenging. Your example is a modified version and
could be something that may be encountered.

before
Acanthus mollis 'Hollard's sister's Gold'
after
Acanthus mollis Hollard's sister's Gold


Pat


"Niek Otten" wrote in message
...
Hi Pat,

Seems difficult; we need more info on the rules.

For example, in:

Acanthus mollis 'Hollard's sister's Gold'

Which apostrophe do you think ought to be removed and why?

--
Kind regards,

Niek Otten


"Pat" wrote in message
...
Other that use find and replace to remove hyphens in a sheet is there
another way to remove hyphens in only certain parts of a string. Here
are
some examples of what i want to do:


before
Acalypha hispida 'Alba'
after
Acalypha hispida Alba

before
Acanthus mollis 'Hollard's Gold'
after
Acanthus mollis Hollard's Gold

before
'Acer campestre Postelense'
after
Acer campestre Postelense

As you can see there are several options. Can a formula take all these
criteria's into account, particularly the second example where a hyphen
needs to be retained.

Pat

Satisfying all the above examples, which may be reduced to the rule:
'remove all hyphens other that that preceding an s', you can use:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D9,"'s","~"),"'" ,""),"~","'s")

replace d9 with the cell your evaluating,
if necessary, replace ~ with any character that wont be appearing in
the source cells being evaluated.

Regards, Sam

Thankyou folks for your help. That worked perfectly.

Pat

"Sam" wrote in message
oups.com...
Satisfying all the above examples, which may be reduced to the rule:
'remove all hyphens other that that preceding an s', you can use:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D9,"'s","~"),"'" ,""),"~","'s")

replace d9 with the cell your evaluating,
if necessary, replace ~ with any character that wont be appearing in
the source cells being evaluated.

Regards, Sam

On Thu, 20 Oct 2005 21:42:56 +0100, "Pat" wrote:

Other that use find and replace to remove hyphens in a sheet is there
another way to remove hyphens in only certain parts of a string. Here are
some examples of what i want to do:


before
Acalypha hispida 'Alba'
after
Acalypha hispida Alba

before
Acanthus mollis 'Hollard's Gold'
after
Acanthus mollis Hollard's Gold

before
'Acer campestre Postelense'
after
Acer campestre Postelense

As you can see there are several options. Can a formula take all these
criteria's into account, particularly the second example where a hyphen
needs to be retained.

Pat




What are your rules here?

In the examples, you are removing apostrophe's (or single quotes) in instances
where they are being used in place of quote marks (or double quotes), and
retaining them where they indicate a possessive. But there are other uses for
this symbol which you do not address in your examples.

If that is what you wish, then I would think you'd need some sophisticated
grammatical analysis to figure out if the apostrophe or single quote is being
used as a quote mark, or as something else (possessive, plural of abbreviation,
absent letters, etc). Possessive plurals may be difficult, too.

He said: 'Where is John's overcoat? Is it o'er there by the Jameses' camping
area?'

John's overcoat is o'er by the Jameses' camping area.

This is a large group of M.D.'s.

and so forth.


--ron

Ron, I can see what you are driving at but the solution I received from Sam
does all that i need at this stage. If i need a more advanced solution then
perhaps I may need to call on you.

Pat


"Ron Rosenfeld" wrote in message
...
On Thu, 20 Oct 2005 21:42:56 +0100, "Pat"
wrote:

Other that use find and replace to remove hyphens in a sheet is there
another way to remove hyphens in only certain parts of a string. Here
are
some examples of what i want to do:


before
Acalypha hispida 'Alba'
after
Acalypha hispida Alba

before
Acanthus mollis 'Hollard's Gold'
after
Acanthus mollis Hollard's Gold

before
'Acer campestre Postelense'
after
Acer campestre Postelense

As you can see there are several options. Can a formula take all these
criteria's into account, particularly the second example where a hyphen
needs to be retained.

Pat




What are your rules here?

In the examples, you are removing apostrophe's (or single quotes) in
instances
where they are being used in place of quote marks (or double quotes), and
retaining them where they indicate a possessive. But there are other uses
for
this symbol which you do not address in your examples.

If that is what you wish, then I would think you'd need some sophisticated
grammatical analysis to figure out if the apostrophe or single quote is
being
used as a quote mark, or as something else (possessive, plural of
abbreviation,
absent letters, etc). Possessive plurals may be difficult, too.

He said: 'Where is John's overcoat? Is it o'er there by the Jameses'
camping
area?'

John's overcoat is o'er by the Jameses' camping area.

This is a large group of M.D.'s.

and so forth.


--ron

On Fri, 21 Oct 2005 11:05:34 +0100, "Pat" wrote:

Ron, I can see what you are driving at but the solution I received from Sam
does all that i need at this stage. If i need a more advanced solution then
perhaps I may need to call on you.

Pat

I don't know that I'd be able to help you with a more advanced solution, but we
could try to take a stab at it.
--ron