Need to separate and rearrange names

  • Follow


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:6FF123F5-17FF-4617-9449-57419E6EC05B@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:6FF123F5-17FF-4617-9449-57419E6EC05B@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:6FF123F5-17FF-4617-9449-57419E6EC05B@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:6FF123F5-17FF-4617-9449-57419E6EC05B@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:6FF123F5-17FF-4617-9449-57419E6EC05B@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:6FF123F5-17FF-4617-9449-57419E6EC05B@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:3EF557DE-50AA-4EEF-807A-99972E8E60C3@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
921 Views

(page loaded in 0.091 seconds)

10/19/2014 11:08:17 PM


Reply: