Changing the order of two names in a cell

[Liz]

I work in payroll and our HR person sent me a spreadsheet
in which she showed employee names as first and last name
(ie. Mary Smith). I need to show the name as last name
then first name (ie. Smith, Mary). How can I do this?

Liz

 

[Liz]

I'm a little confused....what do you mean?

-----Original Message-----
One Accessory,Open it

 

[Guest]

I work in payroll and our HR person sent me a spreadsheet
in which she showed employee names as first and last name
(ie. Mary Smith). I need to show the name as last name
then first name (ie. Smith, Mary). How can I do this?

 

[Norman Harker]

Hi Liz!

One way:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)
If A1 contains Mary Smith:
Returns: Smith, Mary

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Sandra]

What if the persons name includes an initial?
i.e. Mary J. Smith

 

[Norman Harker]

Hi Sandra!

You can use:

=SUBSTITUTE(MID(A1,FIND(".",A1)+2,100),".","")&"
"&LEFT(A1,FIND(".",A1))

But if you have a mixture of these, you might be best off using Data >
Text to columns

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harlan Grove]

I'm a little confused....what do you mean?

...

The previous respondent attached an Excel workbook to his/her response, and
intends for you to open it. Generally (and I doubt this is an exception) it's a
very bad idea to open unsolicited files from people you don't know. It's also
true (and beneficial for you) that Microsoft's CDO interface, which you're using
to read this newsgroup, deletes file attachments from newsgroup posts. The
previous respondent is obviously ignorant of this. Let that be an indicator of
that respondent's general knowledge (i.e., anyone who knows anything knows that
they shouldn't attach binary files to postings in nonbinary newsgroups).

 

[curllion]

the formula: =CONCATENATE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),"
",LEFT(A1,FIND(" ",A1,1)-1))
If "A1"=Mary Smith ,then return Smith Mary