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