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