formula question 1

[mscallisto]

In Excel, given the following example:
A B
1 john smith smith john
2 closet-b closet-b

The formula in B1 and B2, that reverses the names in A1 and A2, if the name is comprised of two parts is is:

= IF(ISERROR(SEARCH(" ",D4,1)),D4,CONCATENATE(RIGHT(D4,LEN(D4)-SEARCH(" ",D4,1))," ",LEFT(D4,SEARCH(" ",D4,1)-1 )))

If the name is less than two parts or blank col A and B are the same.


Now if I add to the problem the following:


A B
1 Mr. john smith smith
2 Mr. & Mrs Jones Jones
3 Mr Smith smith
4 Ms. John J. Smith smith

Is there a "Formula" method to extract the last "part" in this case the last name as shown above in B1 to B4?
I can't seem to find a way other than some vb code.

 

[sfvb]

This is a solution, I'm sure there are others.

=IF(LEN(TRIM(D4))-LEN(TRIM(SUBSTITUTE(D4," ","&quot))>0,SUBSTITUTE(TRIM(D4),LEFT(TRIM(D4),FIND(CHAR(7),SUBSTITUTE(TRIM(D4), " ",CHAR(7),LEN(TRIM(D4))-LEN(SUBSTITUTE(TRIM(D4)," ","&quot)))),"&quot,D4)

But, if you are trying to parse out the last name, then you are going to have problems with people who have a last name like Van Buren, Mc Bride, De La Cruz, St John, etc...

 

[mscallisto]

Very Very nifty!!