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
>