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

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

--
Message posted via http://www.officekb.com

Have you tried "Text to Columns"?

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

Check off "Space"

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

"Fam via OfficeKB.com" <u18245@uwe> wrote in message
news:6113c58e9d8fc@uwe...
> 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
>
> --
> Message posted via http://www.officekb.com

thanks Jeff

Jeff Standen wrote:
>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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

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?

Fam wrote:
>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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

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

"Fam via OfficeKB.com" <u18245@uwe> wrote in message
news:61143504495eb@uwe...
>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?
>
> Fam wrote:
>>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
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200605/1

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


"Jeff Standen" <[email protected]> wrote in message
news:e5%[email protected]...
> 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
>
> "Fam via OfficeKB.com" <u18245@uwe> wrote in message
> news:61143504495eb@uwe...
> >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?
> >
> > Fam wrote:
> >>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
> >
> > --
> > Message posted via OfficeKB.com
> > http://www.officekb.com/Uwe/Forums.a...mming/200605/1
>
>