formula to remove a space and replace but depending on the details

[tonywatsonhelp]

Hi Everyone,

Ok so please help with this formula if you can?

I have names in column B

I need to change the way they are recorded for our new system.

In colunm "B" I have names that are all set up as either sir name and first name or sirname and initial.

I need to reove the space between these words and follow this simple rule.

If the name in the cell (say"B6") has only one digit after the space then replace the space with a full stop "."

examples:

Tony W becomes Tony.W
Jon K = Jon.k
Billy X = Billy.X etc

but if its more than 1 digit remove the space

So:

Tony Wills =TonyWills
Jon Ko = JonKO
Billy Xavia = BillyXavia

please help if you can.
thanks

Tony

 

[Fluff]

One option
=IF(SEARCH(" ",B6)=LEN(B6)-1,SUBSTITUTE(B6," ","."),SUBSTITUTE(B6," ",""))

 

[tonywatsonhelp]

Thank you Fluff, this is great

 

[Scott Huish]

Here's another way:
=SUBSTITUTE(A1," ",IF(LEFT(RIGHT(A1,2))=" ",".",""))