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