Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat a cell from last name, first to first space last?
I downloaded a spreadsheet with a column in it containing peoples names.
They are listed with the last name first then a comma and the first name or first initial. I want to change them to first name and last name sparated by a space for data base purposes to use in a mailing. Is there any easy way to do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat a cell from last name, first to first space last?
Shannon
You may find it is better to have the names in separate cells rather than change to first, last in one cell. Most third-party apps prefer it this way. You could use DataText to Columns space delimited to split into two cells. If you want to swap in-cell, see Chip Pearson's site for formulas. http://www.cpearson.com/excel/FirstLast.htm Gord Dibben MS Excel MVP On Sat, 24 Jun 2006 09:14:01 -0700, Shannon wrote: I downloaded a spreadsheet with a column in it containing peoples names. They are listed with the last name first then a comma and the first name or first initial. I want to change them to first name and last name sparated by a space for data base purposes to use in a mailing. Is there any easy way to do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat a cell from last name, first to first space last?
Nel post
*Shannon* ha scritto: I downloaded a spreadsheet with a column in it containing peoples names. They are listed with the last name first then a comma and the first name or first initial. I want to change them to first name and last name sparated by a space for data base purposes to use in a mailing. Is there any easy way to do this? Hi Shannon, suppose you have a name in D9, you can copy this formula in E9: =MID(D9,FIND(" ",D9)+1,LEN(D9)-FIND(" ",D9))&" "&LEFT(D9,FIND(",",D9)-1) then fill down to rearrange all the names. At the end, if you want to delete the original column you have to first Copy & Past Special, Values the column with the rearranged names. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat a cell from last name, first to first space last?
Hi Shannon,
Let's say you have a name (in the "LastName, Firstname" format) in cell A1, you can put this formula in another cell to get the "Firstname Lastname" format you desi =RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(",",TRIM(A1)))&" "&LEFT(TRIM(A1),FIND(",",TRIM(A1))-1) (note that the trim function is not necessary if you know for sure that your original name text will have no leading or trailing spaces) "Shannon" wrote: I downloaded a spreadsheet with a column in it containing peoples names. They are listed with the last name first then a comma and the first name or first initial. I want to change them to first name and last name sparated by a space for data base purposes to use in a mailing. Is there any easy way to do this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat a cell from last name, first to first space last?
Gord:
After you do text to columns, can't you just concatenate the two cells (in reverse, obviously), adding a space in between? "Gord Dibben" wrote: Shannon You may find it is better to have the names in separate cells rather than change to first, last in one cell. Most third-party apps prefer it this way. You could use DataText to Columns space delimited to split into two cells. If you want to swap in-cell, see Chip Pearson's site for formulas. http://www.cpearson.com/excel/FirstLast.htm Gord Dibben MS Excel MVP On Sat, 24 Jun 2006 09:14:01 -0700, Shannon wrote: I downloaded a spreadsheet with a column in it containing peoples names. They are listed with the last name first then a comma and the first name or first initial. I want to change them to first name and last name sparated by a space for data base purposes to use in a mailing. Is there any easy way to do this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat a cell from last name, first to first space last?
Yes, that method could be used as easily as Chip's formulas.
But Chip's formulas do not require the names to be split up first, so one less step. Gord Dibben MS Excel MVP On Sat, 24 Jun 2006 10:30:02 -0700, bman342 wrote: Gord: After you do text to columns, can't you just concatenate the two cells (in reverse, obviously), adding a space in between? "Gord Dibben" wrote: Shannon You may find it is better to have the names in separate cells rather than change to first, last in one cell. Most third-party apps prefer it this way. You could use DataText to Columns space delimited to split into two cells. If you want to swap in-cell, see Chip Pearson's site for formulas. http://www.cpearson.com/excel/FirstLast.htm Gord Dibben MS Excel MVP On Sat, 24 Jun 2006 09:14:01 -0700, Shannon wrote: I downloaded a spreadsheet with a column in it containing peoples names. They are listed with the last name first then a comma and the first name or first initial. I want to change them to first name and last name sparated by a space for data base purposes to use in a mailing. Is there any easy way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
Don't allow a space in a cell...how to prevent? | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |