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.


0




Reply

Utf

4/1/2010 8:43:01 PM 

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 <Nadine@discussions.microsoft.com>
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.


0




Reply

Gord

4/1/2010 9:03:12 PM


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

HTH
Bob
"Nadine" <Nadine@discussions.microsoft.com> wrote in message
news:6FF123F517FF4617944957419E6EC05B@microsoft.com...
> 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.


0




Reply

Bob

4/1/2010 9:04:15 PM


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" <Nadine@discussions.microsoft.com> wrote in message
news:6FF123F517FF4617944957419E6EC05B@microsoft.com...
> 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.


0




Reply

Rick

4/1/2010 9:14:22 PM


=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.


0




Reply

Utf

4/1/2010 9:15:37 PM


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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
news:#4QZRBe0KHA.6068@TK2MSFTNGP04.phx.gbl...
> 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" <Nadine@discussions.microsoft.com> wrote in message
> news:6FF123F517FF4617944957419E6EC05B@microsoft.com...
>> 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.
>


0




Reply

Rick

4/1/2010 9:17:46 PM


Perfect! THank you so much.
"Bob Phillips" wrote:
> Try
>
> =TRIM(MID(A1,FIND(",",A1)+1,99))&" "&LEFT(A1,FIND(",",A1)1)
>
> 
>
> HTH
>
> Bob
>
> "Nadine" <Nadine@discussions.microsoft.com> wrote in message
> news:6FF123F517FF4617944957419E6EC05B@microsoft.com...
> > 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.
>
>
> .
>


0




Reply

Utf

4/1/2010 9:56:01 PM


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.


0




Reply

Utf

4/1/2010 9:56:05 PM


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" <Nadine@discussions.microsoft.com> wrote in message
> news:6FF123F517FF4617944957419E6EC05B@microsoft.com...
> > 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.
>
> .
>


0




Reply

Utf

4/1/2010 9:58:01 PM


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.


0




Reply

Utf

4/1/2010 9:59:01 PM


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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
> news:#4QZRBe0KHA.6068@TK2MSFTNGP04.phx.gbl...
> > 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" <Nadine@discussions.microsoft.com> wrote in message
> > news:6FF123F517FF4617944957419E6EC05B@microsoft.com...
> >> 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.
> >
> .
>


0




Reply

Utf

4/1/2010 10:01:01 PM


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

HTH
Bob
"Nadine" <Nadine@discussions.microsoft.com> wrote in message
news:3EF557DE50AA4EEF807A99972E8E60C3@microsoft.com...
> 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.


0




Reply

Bob

4/1/2010 10:36:26 PM



11 Replies
1026 Views
(page loaded in 0.013 seconds)
