Extract Last and First Names

[John Grossman]

In the sort column it has all of the names as (Last Name), (First Name).
For instance - Smith, James. Is there a formula where I could extract the
last name and the first name into separate columns?

 

[Peo Sjoblom]

Fastest way would be to use data>text to columns, delimited, next,
comma as delimiter, click finish. Note that you need an empty column
to the right or else anything in that column will be overwritten.

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi
see: http://www.cpearson.com/excel/FirstLast.htm
for a formula approach

 

[David McRitchie]

Hi Peo,
One little annoyance would be that
|Grossman, John|| would become
|Grossman| John| note the space

You can eliminate the space with the TRIMALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

A macro such as Chip's would take care of the trimming.

 

[Peo Sjoblom]

True, you can remove that leading space by using comma and space as
delimiters

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hi Peo,
One little annoyance would be that
|Grossman, John|| would become
|Grossman| John| note the space

You can eliminate the space with the TRIMALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

A macro such as Chip's would take care of the trimming.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Fastest way would be to use data>text to columns, delimited, next,
comma as delimiter, click finish. Note that you need an empty column
to the right or else anything in that column will be overwritten.

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[David McRitchie]

Funny I know I've checked multiple boxes TAB and comma,
or TAB and space but I never thought of data as actually
having both and that if the data looked like it had consecutive
delimiters that the wizard actually checkmarked the consecutive
delimiters as one. Thought they had to be the same
kind of delimiter.

In reality I would not split on spaces since there are probably
a mixture of names with and without middle names.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

True, you can remove that leading space by using comma and space as
delimiters

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hi Peo,
One little annoyance would be that
|Grossman, John|| would become
|Grossman| John| note the space

You can eliminate the space with the TRIMALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

A macro such as Chip's would take care of the trimming.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Fastest way would be to use data>text to columns, delimited, next,
comma as delimiter, click finish. Note that you need an empty column
to the right or else anything in that column will be overwritten.

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

In the sort column it has all of the names as (Last Name), (First Name).
For instance - Smith, James. Is there a formula where I could extract the
last name and the first name into separate columns?

 

[Peo Sjoblom]

I agree, I never use that anyway. Normally I would probably
use a comma and trimall and just adjust manually for the corny names <bg>

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Funny I know I've checked multiple boxes TAB and comma,
or TAB and space but I never thought of data as actually
having both and that if the data looked like it had consecutive
delimiters that the wizard actually checkmarked the consecutive
delimiters as one. Thought they had to be the same
kind of delimiter.

In reality I would not split on spaces since there are probably
a mixture of names with and without middle names.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

True, you can remove that leading space by using comma and space as
delimiters

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hi Peo,
One little annoyance would be that
|Grossman, John|| would become
|Grossman| John| note the space

You can eliminate the space with the TRIMALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

A macro such as Chip's would take care of the trimming.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Fastest way would be to use data>text to columns, delimited, next,
comma as delimiter, click finish. Note that you need an empty column
to the right or else anything in that column will be overwritten.

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

In the sort column it has all of the names as (Last Name), (First Name).
For instance - Smith, James. Is there a formula where I could

extract
the

last name and the first name into separate columns?