software

I have the first and last names in one cell and would like to extract them
into a format that I can sort such as this quot;lastname, firstnamequot;. Is there a
way to do this?
Datagt;Text To Columns with a space delimiter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;David Batemanquot; gt; wrote in message
...
gt; I have the first and last names in one cell and would like to extract them
gt; into a format that I can sort such as this quot;lastname, firstnamequot;. Is there
a
gt; way to do this?
gt;
gt;
Hi,

While the delimited option suggested by Bob is the best way to accomplish
this, a formula solution (Ctrl Shift Enter) is:

The formula assumed that the nameis in cell A16 (For e.g. Ashish Mathur)

MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PR OPER(MID(A16,ROW($2:$13),1))),0) 2,255)amp;quot;,
quot;amp;MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($1:$13),1), PROPER(MID(A16,ROW($1:$13),1))),0),MATCH(TRUE,EXAC T(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13 ),1))),0))

Regards,

quot;David Batemanquot; wrote:

gt; I have the first and last names in one cell and would like to extract them
gt; into a format that I can sort such as this quot;lastname, firstnamequot;. Is there a
gt; way to do this?
gt;
gt;
gt;

On Sat, 18 Feb 2006 08:42:16 -0800, quot;David Batemanquot; gt;
wrote:

gt;I have the first and last names in one cell and would like to extract them
gt;into a format that I can sort such as this quot;lastname, firstnamequot;. Is there a
gt;way to do this?
gt;

Assuming you have no titles or suffixes, you could extract them in the format
you specify in various ways, some of which have already been posted.

You could also download and install Longre's free morefunc.xll add-in from
xcell05.free.fr/

Then use the formula:

=REGEX.MID(A1,quot;\w quot;,-1)amp;quot;, quot;amp;REGEX.MID(A1,quot;.*(?=\s\w \W*$)quot;)--ron