Remove hyphens in text

[Pat]

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

 

[Niek Otten]

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?

 

[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

 

[Sam]

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

 

[Ron Rosenfeld]

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

 

[Pat]

Thankyou folks for your help. That worked perfectly.

Pat

 

[Pat]

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]

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