Or maybe not...

=LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^",
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

<bg

Ragdyer wrote:

Slightly different formula, however with exactly the same concept:

=LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

The line wrap should be different here.

The only 2 spaces in this formula are between the quotes that follow A1.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom G." wrote in message
...
I am receiving an #VALUE error.

Thanks bob, from what I can understand, you are doing a "find" for the ~,
however, this character does not exist. Am I missing something. I am
looking
to always use the last word in the column (there is never a comma
delineating the different names - only text).

TIA.


"Bob Phillips" wrote in message
...
=MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))))+1,255)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tom G." wrote in message
...
I have a column with a variety of first and last name variations:

Joe Smith
Joe John Jones
Tom Jack Stuart Apple
Maria Lois Lane
Johann Sebastian Bach


I need to be able to sort them on their last name. Note that there is
no
comma separating their names and that there can be any number of names,
HOWEVER, the last name always appears LAST.

Probably, in order to make some sort of order out of this mess, it
would
be
best if I create two separate columns: first names, last name.

Any help will be appreciated.

--


cheers,
Tom G







--

Dave Peterson