Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing Text
I have a list of names that are printed like this
SMITH,SAM J CAMPBELL,SUSAN LYNN JONES,CINDY I want it to look like this - Smith, Sam Campbell, Susan Jones, Cindy How do I remove those middle names, put the names in proper form and put a space after the comma? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing Text
=IF(ISERROR(FIND(" ",A1)),SUBSTITUTE(PROPER(A1),",",",
"),SUBSTITUTE(PROPER(LEFT(A1,FIND(" ",A1)-1)),",",", ")) -- Gary''s Student - gsnu200750 "Help" wrote: I have a list of names that are printed like this SMITH,SAM J CAMPBELL,SUSAN LYNN JONES,CINDY I want it to look like this - Smith, Sam Campbell, Susan Jones, Cindy How do I remove those middle names, put the names in proper form and put a space after the comma? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing Text
Select the column of names and click DATA in the menu and select TEXT TO
COLUMNS. The first name is in column 1, the first name is in column 2 and the middle name is in column 3 if the middle name exists. Then in column 4 use the following function to concatenate the last and first name and put them in proper case: =PROPER(A1&" "&B1) Then copy down the column. Then copy the entire column of concatenated text and then click EDIT, select PASTE SPECIAL, click the VALUES option and click OK. Then delete the columns 1, 2 and 3 -- Kevin Backmann "Help" wrote: I have a list of names that are printed like this SMITH,SAM J CAMPBELL,SUSAN LYNN JONES,CINDY I want it to look like this - Smith, Sam Campbell, Susan Jones, Cindy How do I remove those middle names, put the names in proper form and put a space after the comma? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing Text
=PROPER(SUBSTITUTE(LEFT(A1,IF(ISNUMBER(FIND(" ",A1)),FIND("
",A1)-1,LEN(A1))),",",", ")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Help" wrote in message ... I have a list of names that are printed like this SMITH,SAM J CAMPBELL,SUSAN LYNN JONES,CINDY I want it to look like this - Smith, Sam Campbell, Susan Jones, Cindy How do I remove those middle names, put the names in proper form and put a space after the comma? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing Text
Thanks Kevin. That worked easily and perfectly.
"Kevin B" wrote: Select the column of names and click DATA in the menu and select TEXT TO COLUMNS. The first name is in column 1, the first name is in column 2 and the middle name is in column 3 if the middle name exists. Then in column 4 use the following function to concatenate the last and first name and put them in proper case: =PROPER(A1&" "&B1) Then copy down the column. Then copy the entire column of concatenated text and then click EDIT, select PASTE SPECIAL, click the VALUES option and click OK. Then delete the columns 1, 2 and 3 -- Kevin Backmann "Help" wrote: I have a list of names that are printed like this SMITH,SAM J CAMPBELL,SUSAN LYNN JONES,CINDY I want it to look like this - Smith, Sam Campbell, Susan Jones, Cindy How do I remove those middle names, put the names in proper form and put a space after the comma? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing text after concatenate | Excel Worksheet Functions | |||
what download do i need to enable editing text? | Excel Discussion (Misc queries) | |||
Editing Repeated Text | Excel Worksheet Functions | |||
text editing function help | Excel Worksheet Functions | |||
Why does the cell I'm editing put the text on the right side? | Excel Discussion (Misc queries) |