Yet Another Name Format Question

  • Thread starter Thread starter Dallas PM
  • Start date Start date
D

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.
 
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.
Click to expand...

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
 
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
Click to expand...


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
 
Back
Top