Extract specific text
Here is a slightly shorter formula . . .
...
=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2
-FIND(", ",A1))&MID(A1,FIND(" ",A1,1+FIND(" ",A1)),FIND(", ",A1)
-FIND(" ",A1,1+FIND(" ",A1)))
It won't be shorter when you wrap each A1 reference inside TRIM. Safer to
use TRIM(A1) than just A1.
Then again, if you assume there'd only ever be ", " between last name and
first name, you could use
=REPLACE(LEFT(TRIM(A1),FIND(" ",TRIM(A1),FIND(", ",TRIM(A1))+2)-1),
1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")
or putting the result in first-name last-name order
=LEFT(MID(TRIM(A1),FIND(", ",TRIM(A1))+2,255),FIND(" ",MID(TRIM(A1),
FIND(", ",TRIM(A1))+2,255)))&REPLACE(LEFT(TRIM(A1),
FIND(", ",TRIM(A1))-1),1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")
That formula seems to still be dependent on token numbers... let's remove
that dependency. This formula **only** requires the last and first names to
be separated by the first comma-space pair in the string...
=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2-FIND(", ",A1))&"
"&REPLACE(SUBSTITUTE(LEFT(" "&A1,1+FIND(", ",A1)-1),"
",CHAR(1),LEN(LEFT(A1,FIND(", ",A1)))-LEN(SUBSTITUTE((LEFT(A1,FIND(",
",A1)))," ",""))+1),1,FIND(CHAR(1),SUBSTITUTE(LEFT(" "&A1,1+FIND(",
",A1)-1)," ",CHAR(1),LEN(LEFT(A1,FIND(",
",A1)))-LEN(SUBSTITUTE((LEFT(A1,FIND(", ",A1)))," ",""))+1)),"")
Now, I am pretty sure I have some excess function calls in here, but it is
5:30 AM here and I am going to sleep. If you haven't removed the excess for
me when I return, I'll attempt it when I get up. Oh, and I tried this
formula with multiple spaces and it looks like it doesn't need any TRIM
function calls. Okay, good night, I'll "see" you tomorrow.
Rick
|