Separate first and second name in one cell into separate cells.

My list has the first and last names in the same cell. How can I separate
these into two separate cells so I can sort by last name. Or is there a way
to sort by the last name when the name in the cell is "firstname lastname"?

To pull out the first name try this.

=LEFT(B3,FIND(" ",B3)-1)

where b3 is the cell with the name.

To pull out the last name try this.

=MID(B3,FIND(" ",B3)+1,LEN(B3))


Hope this helps.

Hi Dwight

if you want to keep a copy of the combined names, copy this column to a new
column before doing the following:

insert a blank column to the right of the column with the combined names
select the column of combined names
choose data / text to columns
ensure delimited is checked - NEXT
untick tab, tick space
FINISH
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Dwight in Georgia" <Dwight in Georgia@discussions.microsoft.com> wrote in
message news:2AEB2A9D-F5AE-4C9E-A8DA-48C3CA40B3D5@microsoft.com...
> My list has the first and last names in the same cell. How can I separate
> these into two separate cells so I can sort by last name. Or is there a
> way
> to sort by the last name when the name in the cell is "firstname
> lastname"?

"Dwight in Georgia" wrote:

> My list has the first and last names in the same cell. How can I separate
> these into two separate cells so I can sort by last name. Or is there a way
> to sort by the last name when the name in the cell is "firstname lastname"?

Data / text to columns / Delimited / Tick 'space' as delimiter, and this
will put the data into 2 separate columns.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"MaloMan" <MaloMan@discussions.microsoft.com> wrote in message
news:BC4E41B8-E1A5-497D-8D0C-168279BC8534@microsoft.com...
>
>
> "Dwight in Georgia" wrote:
>
>> My list has the first and last names in the same cell. How can I
>> separate
>> these into two separate cells so I can sort by last name. Or is there a
>> way
>> to sort by the last name when the name in the cell is "firstname
>> lastname"?