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

[Dwight in Georgia]

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"?

[stvgarner]

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.

[JulieD]

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 [email protected]> wrote in
message news:[email protected]...
> 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"?

[MaloMan]

"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"?

[Ken Wright]

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> "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"?