converting cell data / opposite?

  • Thread starter Thread starter Jorge
  • Start date Start date
J

Jorge

Hello Excel gurus...

I have a cell/column containing a persons name:
John Doe

I'd like to convert cell/column to read:
Doe, John

What formula is used in order to make this happen?
I've tried left/right/len commands out the wazzoo (technical word) - I
give... uncle....

Any help?
Jorge
 
Hi Jorge

in another cell use
=RIGHT(C14,FIND(" ",C14)-1) &", " & LEFT(C14,FIND(" ",C14))

where C14 has the original data in it

Cheers
JulieD
 
Julie (thank you)

Works okay except for some reason longer names it cutting off first few
xters?

ie
Walter Robinson
returns result
binson, Walter

Any clue?
Jorge
 
Hi Jorge

sorry it's

=RIGHT(C14,LEN(C14)-FIND(" ",C14)) &", " & LEFT(C14,FIND(" ",C14))

(second time today i've made the same mistake!)

Cheers
JulieD
 
Jorge,

You may find you'll need the first and last names separated into separate
columns, such as to do a sort by last name. You might want to consider
converting your table. Here are two formulas, just parts of Julie's

Last Name: =RIGHT(C14,LEN(C14)-FIND(" ",C14))
First Name: =LEFT(C14,FIND(" ",C14))

You could put these in a couple of columns (copied down with the fill handle
as necessary), then you could make it permananent, allowing you to remove
the original column with the full names. Select the columns, Copy, then
with them still selected, Edit - Paste Special - Values. Now remove the
original full name column.

Where needed, you could combine them into full names. If the last name in
C15, and the First name in C16, you could use

Doe, John: =C15 & ", " & C16
John Doe: =C16 & " " & C15
--
Earl Kiosterud
mvpearl omitthisword at verizon period net

President Bush on sovereignty:
http://www.tastefullystated.com/sovereignty.wmv
 
Back
Top