In Excel 2003, I am referencing a text string and would like to return the
text string with only the first letter capitalized. The PROPER function does
not do this, as it capitalizes the first letter of every word. The formula
below does however perform this, but I would like to write it as a
User-Defined Function, similar to the Proper function, so it can be applied
to multiple different workbooks:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

Is it even possible to write a User-Defined Function to complete this
seemingly simple task and, if so, how do I do it?

Thanks,
Chris

Try code like

Function ChangeCase(S As String) As String
ChangeCase = UCase(Left(S, 1)) & LCase(Mid(S, 2))
End Function

Then, call it from a cell with

=ChangeCase(A1)


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 2 Dec 2008 13:18:01 -0800, ChrisHT
wrote:

In Excel 2003, I am referencing a text string and would like to return the
text string with only the first letter capitalized. The PROPER function does
not do this, as it capitalizes the first letter of every word. The formula
below does however perform this, but I would like to write it as a
User-Defined Function, similar to the Proper function, so it can be applied
to multiple different workbooks:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

Is it even possible to write a User-Defined Function to complete this
seemingly simple task and, if so, how do I do it?

Thanks,
Chris