Capitalize only the first letter in text string

[ChrisHT]

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

 

[MARK858]

Why not use <> 0 then you don't need the Goto?

Sub CapsFirstLetter()

Dim Cell As Variant
For Each Cell In Selection
    
    If Len(Cell.Text) <> 0 Then 
    
    Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Right(Cell.Text, Len(Cell.Text) - 1))

    End If

Next

End Sub

 

[Scott Huish]

A couple of points.

There is no need for .Formula because you are not returning a formula. Also, you can shorten that last bit quite a bit by using Mid instead.

Sub CapsFirstLetter()
Dim Cell As Range
For Each Cell In Selection
    If Cell <> "" Then Cell = UCase(Left(Cell, 1)) & LCase(Mid(Cell, 2))
Next
End Sub

 

[Amanda Wilso]

<tbody style="margin: 0px; padding: 0px; border: 0px;">
[TD="class: votecell"]

up votdown vote​

[/TD]
[TD="class: answercell"]You can also copy necessary data into a Microsoft Word document and change the letter cases using the "Aa" button in the Home > Font tab . Then just copy and paste back into Excel.

Or using the formula:

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

[/TD]

</tbody>