Remove hyphens in text

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" <glass_patrick@hotmail.com> wrote in message
news:ezquuYb1FHA.268@TK2MSFTNGP09.phx.gbl...
> 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" <nicolaus@xs4all.nl> wrote in message
news:O0cf4cb1FHA.2132@TK2MSFTNGP15.phx.gbl...
> 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" <glass_patrick@hotmail.com> wrote in message
> news:ezquuYb1FHA.268@TK2MSFTNGP09.phx.gbl...
> > 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" <mailme@samjacob.co.uk> wrote in message
news:1129844671.043040.153520@g49g2000cwa.googlegroups.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" <glass_patrick@hotmail.com> 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" <ronrosenfeld@nospam.org> wrote in message
news:po3gl1d5gcqk2c6uddmcp6eifqls4ghsu7@4ax.com...
> On Thu, 20 Oct 2005 21:42:56 +0100, "Pat" <glass_patrick@hotmail.com>
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" <glass_patrick@hotmail.com> 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