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.

On Tue, 1 Apr 2008 13:14:03 -0700, Dallas PM
wrote:

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

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

"Ron Rosenfeld" wrote:

On Tue, 1 Apr 2008 13:14:03 -0700, Dallas PM
wrote:

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

On Tue, 01 Apr 2008 16:48:56 -0400, Ron Rosenfeld
wrote:

On Tue, 1 Apr 2008 13:14:03 -0700, Dallas PM
wrote:

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


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