Hi,

I need to parse out a list of names, such that I end up with the last name regardless of whether it's "Mr. So-and-so," "Mr. & Mrs. So-and-so," or any variation thereof. I've been playing with right(), search(), len(), and combinations of them, but just can't get it to work. Basically, what I need to do is count backwards (right to left) to get the position of the last space in the string -- i.e., in front of the last name -- then subtract that number from the length of the entire string, and then use the resulting number in the right() function.

The instructions in Excel Help were okay, as long as all the strings were the same type (Mr. and Mrs., or just Mr., or whatever).

Can anyone get me squared away?

Thanks much.

Okay, I found it online, the only version that seems to work:

=MID(A10,FIND(CHAR(1),SUBSTITUTE(A10," ",CHAR(1),
LEN(A10)-LEN(SUBSTITUTE(A10," ",""))))+1,255)

Funny, I seem to remember being able to do this in BASIC by getting the length of substrings and going from there, but I guess you can't do it in Excel unless you go into VBA.

You could use the text to columns function to get the names, but this sort of conditional parsing is much more suited to VBA than formulas. Is a custom function an option for you?



Curt

Text to columns doesn't work in this case because the names are entered in so many different ways (Mr. and Mrs., Mr. and Mrs. John, John and Cathy, middle initials, and so forth) that the last names would end up in different columns.

A custom function is certainly an option, although the one I found online works really well for my purposes. Actually, I already set it up and merged the letters.

Thanks so much for your interest!

No worries -- I composed my reply while you wrote your follow-up. I certainly couldn't have created the formula you found online, so I'm glad you found a solution.



Curt