Need to separate and rearrange names
in [Functions]
|
Prev: See if cell value appears within a value range given by another cell.
Next: Conditional Formatting: comparing cell values
From: Rick Rothstein on 1 Apr 2010 17:17 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(a)NO.SPAMverizon.net> wrote in message news:#4QZRBe0KHA.6068(a)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(a)discussions.microsoft.com> wrote in message > news:6FF123F5-17FF-4617-9449-57419E6EC05B(a)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. >
From: Nadine on 1 Apr 2010 17:56 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(a)discussions.microsoft.com> wrote in message > news:6FF123F5-17FF-4617-9449-57419E6EC05B(a)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. > > > . >
From: Nadine on 1 Apr 2010 17:56 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.
From: Nadine on 1 Apr 2010 17:58 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(a)discussions.microsoft.com> wrote in message > news:6FF123F5-17FF-4617-9449-57419E6EC05B(a)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. > > . >
From: Nadine on 1 Apr 2010 17:59
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. |