Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with first and last names
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 "lastname, firstname". Is there a way to do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with first and last names
DataText To Columns with a space delimiter
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "David Bateman" wrote in message ... 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 "lastname, firstname". Is there a way to do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with first and last names
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)&", "&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, "David Bateman" wrote: 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 "lastname, firstname". Is there a way to do this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with first and last names
On Sat, 18 Feb 2006 08:42:16 -0800, "David Bateman"
wrote: 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 "lastname, firstname". Is there a way to do this? 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 http://xcell05.free.fr/ Then use the formula: =REGEX.MID(A1,"\w+",-1)&", "®EX.MID(A1,".*(?=\s\w+\W*$)") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|