Need to separate and rearrange names
 

How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the text
it is converting so that the original text remains unchanged.

Need to separate and rearrange names
 

See Chip Pearson's site for rearranging multiple permutations of names.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben MS Excel MVP

On Thu, 1 Apr 2010 13:43:01 -0700, Nadine
wrote:

How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the text
it is converting so that the original text remains unchanged.

Need to separate and rearrange names
 

Try

=TRIM(MID(A1,FIND(",",A1)+1,99))&" "&LEFT(A1,FIND(",",A1)-1)

--

HTH

Bob

"Nadine" wrote in message
...
How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the
text
it is converting so that the original text remains unchanged.

Need to separate and rearrange names
 

Assuming there is no space following the comma (both your examples show
that), give this formula a try...

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))

--
Rick (MVP - Excel)



"Nadine" wrote in message
...
How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the
text
it is converting so that the original text remains unchanged.

Need to separate and rearrange names
 

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))


"Nadine" wrote:

How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the text
it is converting so that the original text remains unchanged.

Need to separate and rearrange names
 

Actually, if your data could be mixed (some with a space after the comma and
some without one), then you can use this formula to handle both of those
conditions...

=TRIM(MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1)))

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Assuming there is no space following the comma (both your examples show
that), give this formula a try...

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))

--
Rick (MVP - Excel)



"Nadine" wrote in message
...
How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the
text
it is converting so that the original text remains unchanged.

Need to separate and rearrange names
 

Perfect! THank you so much.

"Bob Phillips" wrote:

Try

=TRIM(MID(A1,FIND(",",A1)+1,99))&" "&LEFT(A1,FIND(",",A1)-1)

--

HTH

Bob

"Nadine" wrote in message
...
How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the
text
it is converting so that the original text remains unchanged.


.

Need to separate and rearrange names
 

This leaves lots of extra space between the two names. Gord Dibben's formula
worked perfect. Thanks for taking a stab at it though. I appreciate it.

"Teethless mama" wrote:

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))


"Nadine" wrote:

How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the text
it is converting so that the original text remains unchanged.

Need to separate and rearrange names
 

This one leaves lots of spaces between the first and last name.

"Rick Rothstein" wrote:

Assuming there is no space following the comma (both your examples show
that), give this formula a try...

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))

--
Rick (MVP - Excel)



"Nadine" wrote in message
...
How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the
text
it is converting so that the original text remains unchanged.

.

Need to separate and rearrange names
 

That was supposed to say Bob Phillips' post worked perfect.

"Nadine" wrote:

This leaves lots of extra space between the two names. Gord Dibben's formula
worked perfect. Thanks for taking a stab at it though. I appreciate it.

"Teethless mama" wrote:

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))


"Nadine" wrote:

How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the text
it is converting so that the original text remains unchanged.

Need to separate and rearrange names
 

This works. Thanks.

"Rick Rothstein" wrote:

Actually, if your data could be mixed (some with a space after the comma and
some without one), then you can use this formula to handle both of those
conditions...

=TRIM(MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1)))

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Assuming there is no space following the comma (both your examples show
that), give this formula a try...

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))

--
Rick (MVP - Excel)



"Nadine" wrote in message
...
How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the
text
it is converting so that the original text remains unchanged.

.

Need to separate and rearrange names
 

You were lucky, I was about to write to my MP <bg

--

HTH

Bob

"Nadine" wrote in message
...
That was supposed to say Bob Phillips' post worked perfect.

"Nadine" wrote:

This leaves lots of extra space between the two names. Gord Dibben's
formula
worked perfect. Thanks for taking a stab at it though. I appreciate it.

"Teethless mama" wrote:

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))


"Nadine" wrote:

How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than
the text
it is converting so that the original text remains unchanged.