I have a list of names; want to split into 2 columns-surnames & everything
else. format is always: john q. public/john public/j. p. public,etc. no
suffixes, nothing is after surname. All I need to do is count to the first
space from the RIGHT. Can Excel do that?

Thanks!
Kate

On Fri, 17 Feb 2006 10:23:27 -0800, "genkate"
wrote:

I have a list of names; want to split into 2 columns-surnames & everything
else. format is always: john q. public/john public/j. p. public,etc. no
suffixes, nothing is after surname. All I need to do is count to the first
space from the RIGHT. Can Excel do that?

Thanks!
Kate


If the surname is always the last word in the cell, then it can be extracted
using the formula:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255)


--ron

You could use a macro with
instrev
InStrRev Function


Description

Returns the position of an occurrence of one string within another, from the
end of string.

Syntax

InstrRev(stringcheck, stringmatch[, start[, compare]])

The InstrRev function syntax has these named arguments:

Part Description
stringcheck Required. String expression being searched.
stringmatch Required. String expression being searched for.
start Optional. Numeric expression that sets the starting position for
each search. If omitted, -1 is used, which means that the search begins at
the last character position. If start contains
Null, an error occurs.

compare Optional. Numeric value indicating the kind of comparison to
use when evaluating substrings. If omitted, a binary comparison is
performed. See Settings section for values.

--
Don Guillett
SalesAid Software

"genkate" wrote in message
...
I have a list of names; want to split into 2 columns-surnames & everything
else. format is always: john q. public/john public/j. p. public,etc. no
suffixes, nothing is after surname. All I need to do is count to the first
space from the RIGHT. Can Excel do that?

Thanks!
Kate

Yes, it worked great! Is there a way to extract the rest of the name into the
other column? Also, I found that there was a space after the surname. There
must be a way to delete that without deleting all the other spaces. Thank you
so much!
Kate
"Ron Rosenfeld" wrote:

On Fri, 17 Feb 2006 10:23:27 -0800, "genkate"
wrote:

I have a list of names; want to split into 2 columns-surnames & everything
else. format is always: john q. public/john public/j. p. public,etc. no
suffixes, nothing is after surname. All I need to do is count to the first
space from the RIGHT. Can Excel do that?

Thanks!
Kate


If the surname is always the last word in the cell, then it can be extracted
using the formula:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255)


--ron

On Sat, 18 Feb 2006 13:36:30 -0800, "genkate"
wrote:

Yes, it worked great! Is there a way to extract the rest of the name into the
other column?

Same principle using the LEFT function:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))-1)


Also, I found that there was a space after the surname. There
must be a way to delete that without deleting all the other spaces. Thank you
so much!

Is there always a space? Is it a normal space or a no-break space?

If it's a normal space, you could always wrap the Last Name formula in a TRIM
function:

=TRIM(MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255))





Kate
"Ron Rosenfeld" wrote:

On Fri, 17 Feb 2006 10:23:27 -0800, "genkate"
wrote:

I have a list of names; want to split into 2 columns-surnames & everything
else. format is always: john q. public/john public/j. p. public,etc. no
suffixes, nothing is after surname. All I need to do is count to the first
space from the RIGHT. Can Excel do that?

Thanks!
Kate


If the surname is always the last word in the cell, then it can be extracted
using the formula:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255)


--ron


--ron

It must not be a normal space as this doesn't work unless I manually delete
space. However, once that's done, it works great.

Kate

"Ron Rosenfeld" wrote:

On Sat, 18 Feb 2006 13:36:30 -0800, "genkate"
wrote:

Yes, it worked great! Is there a way to extract the rest of the name into the
other column?

Same principle using the LEFT function:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))-1)


Also, I found that there was a space after the surname. There
must be a way to delete that without deleting all the other spaces. Thank you
so much!

Is there always a space? Is it a normal space or a no-break space?

If it's a normal space, you could always wrap the Last Name formula in a TRIM
function:

=TRIM(MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255))





Kate
"Ron Rosenfeld" wrote:

On Fri, 17 Feb 2006 10:23:27 -0800, "genkate"
wrote:

I have a list of names; want to split into 2 columns-surnames & everything
else. format is always: john q. public/john public/j. p. public,etc. no
suffixes, nothing is after surname. All I need to do is count to the first
space from the RIGHT. Can Excel do that?

Thanks!
Kate


If the surname is always the last word in the cell, then it can be extracted
using the formula:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255)


--ron


--ron

On Sat, 18 Feb 2006 20:02:27 -0800, "genkate"
wrote:

It must not be a normal space as this doesn't work unless I manually delete
space. However, once that's done, it works great.

Kate

If it is not a normal space, then it is probably a no-break space (ASCII
character code = 160) common in HTML documents.

To get rid of it in one step:

=SUBSTITUTE(MID(A1,FIND(CHAR(1),
SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))+1,255),
CHAR(160),"")

For simpler formulas, you could use something called regular expressions.

You can download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then, for surname, you could use the formula:

=REGEX.MID(A1,"\w+",-1)

and for displaying everything except the last name:

=REGEX.MID(A1,".*(?=\s+\w+\W*$)")


--ron

That works great! You guys are so smart! I'm glad you're available!

k

"Ron Rosenfeld" wrote:

On Sat, 18 Feb 2006 20:02:27 -0800, "genkate"
wrote:

It must not be a normal space as this doesn't work unless I manually delete
space. However, once that's done, it works great.

Kate

If it is not a normal space, then it is probably a no-break space (ASCII
character code = 160) common in HTML documents.

To get rid of it in one step:

=SUBSTITUTE(MID(A1,FIND(CHAR(1),
SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))+1,255),
CHAR(160),"")

For simpler formulas, you could use something called regular expressions.

You can download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then, for surname, you could use the formula:

=REGEX.MID(A1,"\w+",-1)

and for displaying everything except the last name:

=REGEX.MID(A1,".*(?=\s+\w+\W*$)")


--ron