Combining upper and lower case functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
One way ..

Assume the names are in col A, A2 down,
and are all in the format as posted:
Last, First Middle
Click to expand...

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
Click to expand...

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Teresa said:
If I have a column of names:

Last, First Middle

and I want the last name capitalized, the first letter of the first name
Click to expand...
capitalized, and the middle name or initial gone, how do I combine the
functions?
 
Back
Top