sperate from the space, in text, in the same cell

[Fam via OfficeKB.com]

Hi..
Is there a way tpo seperate or extract out first or last name from the same
cell?
for example, if in cell A1 we have John Smith, can I able top extract out
just John or Smith in cell B1?
Any help will be appreciated
thx

 

[Jeff Standen]

You could use the SEARCH worksheet function. This searches for a specific
character in a cell and returns the position. Combine it with LEFT or MID
(or RIGHT, if you also use LEN) to get the name you want.

=LEFT(A1,SEARCH(" ",A1)-1)

will give you the first name

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

will give you the surname. You will have to be a little more creative if
there are middle names though.

Jeff

 

[Ikaabod]

Have you tried "Text to Columns"?

Data->Text to Columns...->Delimited

Check off "Space"

 

[Ikaabod]

Have you tried "Text to Columns"?

Data->Text to Columns...->Delimited

Check off "Space"

 

[Fam via OfficeKB.com]

thanks Jeff

You could use the SEARCH worksheet function. This searches for a specific
character in a cell and returns the position. Combine it with LEFT or MID
(or RIGHT, if you also use LEN) to get the name you want.

=LEFT(A1,SEARCH(" ",A1)-1)

will give you the first name

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

will give you the surname. You will have to be a little more creative if
there are middle names though.

Jeff

Hi..
Is there a way tpo seperate or extract out first or last name from the

[quoted text clipped - 4 lines]

Any help will be appreciated
thx

 

[Fam via OfficeKB.com]

I able to extract the first name name but what if the situation is like
John Smith, CFO
Now I canextract the first name by
=left(A1, find(" ",A1)-1)
and the title by
=trim(mid(a1,find(" ",A1),255)) but come is still coming with it and I can
not able to resolve how to seperate the last name with the title and extract
it out
any help please?

thanks Jeff

You could use the SEARCH worksheet function. This searches for a specific
character in a cell and returns the position. Combine it with LEFT or MID

[quoted text clipped - 16 lines]

 

[Jeff Standen]

It helps to have a set of nice data but of course that isn't always what
happens. If you know, for instance, that the surname will always be between
the first space and the first comma, you could do this

=MID(A1,SEARCH(" ",A1)+1,SEARCH(",",A1)-SEARCH(" ",A1)-1)

You have to play it by ear a little - you can use the IF function to
seperate different options depending on, say, whether there is a comma in it
or not.

Jeff

I able to extract the first name name but what if the situation is like
John Smith, CFO
Now I canextract the first name by
=left(A1, find(" ",A1)-1)
and the title by
=trim(mid(a1,find(" ",A1),255)) but come is still coming with it and I can
not able to resolve how to seperate the last name with the title and
extract
it out
any help please?

thanks Jeff

You could use the SEARCH worksheet function. This searches for a specific
character in a cell and returns the position. Combine it with LEFT or MID

[quoted text clipped - 16 lines]

Any help will be appreciated
thx

 

[Tom Ogilvy]

How about John van Dyke Smith Jones Sr.

or not to be as outlandish

John Van Dyke, CFO

To the OP, there is no generalized algorithm that is going to do what you
want unless you have a specific pattern or a finite set of unique patterns
that are distinct or the elements of the name are distringuishably
delimited. Usually this isn't the case for names

http://www.cpearson.com/excel/FirstLast.htm

will give some insights.

--
Regards,
Tom Ogilvy

It helps to have a set of nice data but of course that isn't always what
happens. If you know, for instance, that the surname will always be between
the first space and the first comma, you could do this

=MID(A1,SEARCH(" ",A1)+1,SEARCH(",",A1)-SEARCH(" ",A1)-1)

You have to play it by ear a little - you can use the IF function to
seperate different options depending on, say, whether there is a comma in it
or not.

Jeff

I able to extract the first name name but what if the situation is like
John Smith, CFO
Now I canextract the first name by
=left(A1, find(" ",A1)-1)
and the title by
=trim(mid(a1,find(" ",A1),255)) but come is still coming with it and I can
not able to resolve how to seperate the last name with the title and
extract
it out
any help please?

thanks Jeff

You could use the SEARCH worksheet function. This searches for a specific
character in a cell and returns the position. Combine it with LEFT or MID
[quoted text clipped - 16 lines]
Any help will be appreciated
thx