Combining upper and lower case functions

[Teresa]

If I have a column of names:

Last, First Middle

and I want the last name capitalized, the first letter of the first name capitalized, and the middle name or initial gone, how do I combine the functions?

 

[Norman Harker]

Hi Teresa!

One way is to use a helper formula:

=UPPER(LEFT(A1,FIND(",",A1)+1))&PROPER(LEFT(MID(A1,FIND("
",A1)+1,255),FIND(" ",MID(A1,FIND(" ",A1)+1,255))-1))

Another is to use Data > Text to columns to separate out the three
elements
Apply UPPER and PROPER to the Last and First names
Concatenate the results.

 

[Max]

One way ..

Assume the names are in col A, A2 down,
and are all in the format as posted:

Last, First Middle

Try in B2:

=UPPER(LEFT(TRIM(A2),SEARCH(",",TRIM(A2))-1))&",
"&UPPER(MID(TRIM(A2),SEARCH(" ",TRIM(A2))+1,1))&TRIM(MID(TRIM(A2),SEARCH("
",TRIM(A2))+2,SEARCH(" ",TRIM(A2),SEARCH(" ",TRIM(A2))+1)-SEARCH("
",TRIM(A2))-1))

Copy B2 down

Col B should return col A's names in the format desired, i.e.:

Last, First

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

If I have a column of names:

Last, First Middle

and I want the last name capitalized, the first letter of the first name

capitalized, and the middle name or initial gone, how do I combine the
functions?