Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could do
the trick.

Thanks to you all.

=RIGHT(A1,LEN(A1)-FIND(" ",A1))
But I hope you have no middle initials: Winston S Churchill
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Nikki" wrote in message
...
Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can
offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could
do
the trick.

Thanks to you all.

try

=TRIM(MID(A1,FIND(" ",A1),LEN(A1)))

Mike

"Nikki" wrote:

Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could do
the trick.

Thanks to you all.

If you don't have to worry about middle names or a double space in the name..

=RIGHT(E3,len(E3)-find(" ",E3))

"Nikki" wrote:

Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could do
the trick.

Thanks to you all.

Try
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

--
Always provide your feedback...


"Nikki" wrote:

Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could do
the trick.

Thanks to you all.

try this

=MID(E3,FIND(" ",E3)+1,255)

On Oct 17, 12:06*am, Nikki wrote:
Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could do
the trick.

Thanks to you all.

If you do have two-part firt names (John Quincy Adams), see the Excel section
of
http://www.mirecki.us/computing/
--
TedMi

"Nikki" wrote:

Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could do
the trick.

Thanks to you all.

If either first last or first mid last, then

=IF(ISERROR(RIGHT(E3,len(E3)-find(" ",E3,find("
",E3)+1))),RIGHT(E3,len(E3)-find(" ",E3)),RIGHT(E3,len(E3)-find(" ",E3,find("
",E3)+1))))

would account for both.

"Sean Timmons" wrote:

If you don't have to worry about middle names or a double space in the name..

=RIGHT(E3,len(E3)-find(" ",E3))

"Nikki" wrote:

Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could do
the trick.

Thanks to you all.

On Thu, 16 Oct 2008 12:06:41 -0700, Nikki
wrote:

Hello:

I have a name "Jane Doe" that I would like to pull the last name into a
separate column. I have used the mid((e3,1,find(" ",e3,1)-1 formula but it
only gives me Jane. I would like to pull Doe instead. Any help you can offer
is greatly appreciated.

I would like to avoid "Text to Columns" if possible because I will need to
copy the column into a separate worksheet and was hoping a formula could do
the trick.

Thanks to you all.


And just another way of extracting the last word in a string:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

--ron