Assistance with Formula

[volpinex]

Hello,

I need to build the following formula, in column A I have legal name and in column B preferred name.
If the Legal cell is blank, I want to return in C the value from B with two other conditions.
Say in A1 the value is blank, in B we have D.O’Brien.
What I need in C is D O'Brien
I need to substitute the . with a blank space and the slanted apostrophe with a straight one.
The apostrophe is not always in the same spot.
I tried nesting SUBSTITUTE and ISNUMBER but I keep getting VALUE error.
Thank you for your help!

 

[steve the fish]

Hi. Try a nested substitute:

=SUBSTITUTE(SUBSTITUTE(B1,"."," "),"’","'")