Another Text to Columns question

[Martin ©¿©¬]

Hi
I have the name McAllister Mr & Mrs E.
When I use text to columns it becomes Mr & Mrs E.McAllister
However, I want it to be Mr & Mrs E. McAllister
(with a space between E. & McAllister)

How would I do that please?

 

[Jarek Kujawa]

how about:

=MID(A1,LEN(A1)-10,11)&" "&LEFT(A1,DÅ(A1)-11)

 

[David Biddulph]

=RIGHT(A2,LEN(A2)-FIND(" ",A2))&" "&LEFT(A2,FIND(" ",A2)-1)

 

[Jarek Kujawa]

corrected
=MID(A1,LEN(A1)-10,11)&" "&LEFT(A1,LEN(A1)-11)

sorry

 

[Martin ©¿©¬]

Thanks Jarek & David
For some reason that wouldn't work for me, so I went about it a
different way.
I have 284 names that I needed to make labels for, so I imported my
list into Word label merge and inserted a space there

I realise that you were'nt to know the purpose from my original
posting. However, your replies did prompt me to find a work-around

Thank you both