Separating Names

[Guest]

I have a spreadsheet that has first and last names, e.g. John Smith, combined in single cells. I need to separate those names so that the first name and last name appear in different cells. Any suggestions?

 

[Richard O. Neville]

Copy the column with the two names. In the original column, remove each
first name, initial, and space; in the copied column, remove the last name
and any space. Tedious, but I don't know how else to do it.

I have a spreadsheet that has first and last names, e.g. John Smith,

combined in single cells. I need to separate those names so that the first
name and last name appear in different cells. Any suggestions?

 

[Jason Morin]

Select the column of names, go to Data > Text to columns,
and use space as your delimiter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a spreadsheet that has first and last names, e.g.

John Smith, combined in single cells. I need to separate
those names so that the first name and last name appear
in different cells. Any suggestions?

 

[Leo Heuser]

Assuming the last name is the name after the
last space (for a name with more than two names
e.g. Harry Dean Stanton).

First name (Harry Dean)
=IF(ISERROR(SEARCH(" ",A1)),A1,LEFT(A1,SEARCH(CHAR(7),
SUBSTITUTE(A1," ",CHAR(7),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))


Last name (Stanton)
=IF(ISERROR(SEARCH(" ",A1)),A1,RIGHT(A1,LEN(A1)-SEARCH(CHAR(7),
SUBSTITUTE(A1," ",CHAR(7),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

The ISERROR part is for the situation, where the cell
contains only one name (no space)


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

I have a spreadsheet that has first and last names, e.g. John Smith,

combined in single cells. I need to separate those names so that the first
name and last name appear in different cells. Any suggestions?

 

[BenjieLop]

Try this...

Assuming "John Smith" is in Cell A1 and you like Cell B1 to contain
"John" and Cell C1 to contain "Smith"

In Cell B1: =left(A1,find("",A1)-1

In Cell C1: =right(a1,len(a1)-find("",a1)


Good luck.

 

[hgrove]

Leo Heuser wrote...

Assuming the last name is the name after the last space (for a
name with more than two names e.g. Harry Dean Stanton).

...

Granted you've stated your assumptions, but try

Oscar de la Hoya

or perhaps

RAYMOND USES THE KNIFE, JR.

(the name of the current vice chairman of the Cheyenne River Siou
Tribe - with a generational qualifer for added fun).

Better to use Data > Text to Colums, then fix unusual entries manually

 

[hgrove]

hgrove wrote...
...

RAYMOND USES THE KNIFE, JR.

(the name of the current vice chairman of the Cheyenne River
Sioud Tribe - with a generational qualifer for added fun).

...

@#$% typo! That should be the Cheyenne River Sioux Tribe

 

[BenjieLop]

CORRECTION:

I forgot to place the last parenthesis on each formula. So, they shoul
look like these:

In Cell B1: =left(A1,find("",A1)-1)

In Cell C1: =right(A1,len(A1)-find("",A1))

 

[Leo Heuser]

Leo Heuser wrote...
..

Granted you've stated your assumptions, but try

Oscar de la Hoya

or perhaps

RAYMOND USES THE KNIFE, JR.

(the name of the current vice chairman of the Cheyenne River Sioud
Tribe - with a generational qualifer for added fun).

Better to use Data > Text to Colums, then fix unusual entries manually.

Yes, names are interesting stuff, but at least the user
now has a choice of actions.

Some names won't even split:

Jack The Ripper (Well..)
Robin of Locksley
Charles Handsome III
Werner von Braun
Ludwig van Beethoven
Piet Van der Valk

Actually, in Denmark at least, "de la Hoya", "von Braun" and
"van der Falk" are considered last names. Funny enough
"of Locksley" isn't, even if "de la", "von", "van" and "of" roughly
has the same meaning. But you never hear "van Beethoven"!


LeoH

 

[Harlan Grove]

Yes, names are interesting stuff, but at least the user
now has a choice of actions. ....
Actually, in Denmark at least, "de la Hoya", "von Braun" and
"van der Falk" are considered last names. Funny enough
"of Locksley" isn't, even if "de la", "von", "van" and "of" roughly
has the same meaning. But you never hear "van Beethoven"!

And how would you in Denmark write a rule that could also handle

Nguyen Van Diep

?

Names are interesting stuff!

 

[Leo Heuser]

And how would you in Denmark write a rule that could also handle

Nguyen Van Diep

The rules for names in Denmark are:
1. You can have one (and only one) last name.
2. You can have one or more first names.
3. You can have one or more middle names.
4. Middle name(s) are the name(s) between first name(s) and last name.
A middle name is a name, which in principle is a last name (i.e. not a first
name)

The name

Jens Peter Skov Kristensen

has two first names: 'Jens' and 'Peter' because 'Peter' also
is a first name like in the name 'Peter Madsen'

one middle name: 'Skov', because 'Skov' also is a last
name like in the name 'Frank Skov' AND because
'Skov' cannot be found as a first name.

one last name 'Kristensen'

As for 'Nguyen Van Diep'
Since Van is with an uppercase "V", it would be
considered a middle name, because Van as a first name
is not common (if found at all) in Denmark, so one first
name, one middle name and one last name.

I'm not familiar with the American naming conventions, but
since 'Van' is found as a first name (Van Morrison?
(Great singer IMO!)), my guess is that 'Nguyen Van Diep' in
USA would contain two first names and one last name.

All this is pure guesswork, and it's difficult (or may be even
impossible) to lookup foreign names (foreign to this country)
after the national naming convention.

But that's part of names being interesting stuff

BTW in Denmark we're in for a new naming convention in
the near future, a convention which radically differs from the
one, we have now. Not with the above mentioned points, but
in the way we are allowed to create new names.
The 'name pond' is rapidly filling up, so we have to see some
new ways of creating names, if we are not to be crowded
with names like Hansen, Jensen etc. Not that there is anything
wrong with these names, but it is becoming increasingly harder
to see the relationship (if any!) between families with the same
last name.


LeoH