Yet Another Name Format Question

[Dallas PM]

I am running Excel 2003 where cell A1 is John Paul Jones. My task is to
reformat the contents of cell A1 to Jones John Paul in cell B1. Due to the
structure of the worksheet, I cannot add columns or use VBA/macros, so the
solution has to be executed in B1. Any assistance will be greatly appreciated.

 

[Ron Rosenfeld]

I am running Excel 2003 where cell A1 is John Paul Jones. My task is to
reformat the contents of cell A1 to Jones John Paul in cell B1. Due to the
structure of the worksheet, I cannot add columns or use VBA/macros, so the
solution has to be executed in B1. Any assistance will be greatly appreciated.

If I understand you correctly, you want to move the last word in the string in
A1 to the beginning of the string.

The following formula should do that, and, if there is only a single word in
A1, will reproduce it:

=IF(LEN(TRIM(A1))=LEN(SUBSTITUTE(A1," ","")),TRIM(A1),
MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),
LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,255)&" "&
LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),
LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1))
--ron

 

[Dallas PM]

That was exactly what I was looking for!! Thank you so much

 

[Ron Rosenfeld]

If I understand you correctly, you want to move the last word in the string in
A1 to the beginning of the string.

The following formula should do that, and, if there is only a single word in
A1, will reproduce it:

=IF(LEN(TRIM(A1))=LEN(SUBSTITUTE(A1," ","")),TRIM(A1),
MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),
LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,255)&" "&
LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),
LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1))
--ron

Here's a slightly shorter routine. This must be entered as an array-formula
(hold down <ctrl><shift> while hitting <enter>).

I don't know which is "better"

=IF(A1="","",MID(TRIM(A1),MATCH(TRUE,ISERR(FIND(" ",
MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(A1))),255))),0),255)
&" "&LEFT(TRIM(A1),MATCH(TRUE,ISERR(FIND(
" ",MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(A1))),255))),0)-1))

--ron