Changing the order of two names in a cell

  • Thread starter Thread starter Liz
  • Start date Start date
L

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
 
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?
 
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.
 
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.
 
I'm a little confused....what do you mean?
Click to expand...
...

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).
 
the formula: =CONCATENATE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),"
",LEFT(A1,FIND(" ",A1,1)-1))
If "A1"=Mary Smith ,then return Smith Mary
 
Back
Top