Editing Text

[Guest]

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?

 

[Guest]

=IF(ISERROR(FIND(" ",A1)),SUBSTITUTE(PROPER(A1),",",",
"),SUBSTITUTE(PROPER(LEFT(A1,FIND(" ",A1)-1)),",",", "))

 

[Guest]

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

 

[Bob Phillips]

=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)

 

[Guest]

Thanks Kevin. That worked easily and perfectly.

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