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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - sperate from the space, in text, in the same cell (https://www.excelbanter.com/excel-programming/362912-sperate-space-text-same-cell.html)

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

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

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

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

Have you tried "Text to Columns"?

Data-Text to Columns...-Delimited

Check off "Space"

--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=547113

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

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

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

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

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

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

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

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" wrote in message
...
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