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