| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Separate names into columns | Excel Worksheet Functions | |||
| Separate First, Last Names | Excel Discussion (Misc queries) | |||
| need to separate first and last names from one cell to two | Excel Discussion (Misc queries) | |||
| Names and Separate Worksheets | Excel Worksheet Functions | |||
| can i rearrange names in alphabetic order... | Excel Discussion (Misc queries) | |||
Linear Mode
