Capitalize only the first letter in text string

ChrisHT

New Member
Joined
Dec 2, 2008
Messages
2
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
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
A UDF would mean you would have to enter a formula into each cell. It would be a little shorter than the above but still the same effort really.

Would you not be better writing a simple sub that does it for each cell in the selected range?

Something like (Freehand so there may be errors)

Code:
Sub CapsFirstLetter()
Dim Cell As Variant
For Each Cell In Selection
    Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Right(Cell.Text, Len(Cell.Text) - 1))
Next
End Sub
Save that to your personal workbook, assign it to a button or shortcut key, select your range and hit the button or shortcut key.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
UDF
Code:
Function SentenceCase(txt As String) As String
Dim e
For Each e In Split(txt, ".")
    SentenceCase = SentenceCase & ". " & UCase(Left$(Trim(e),1)) & _
        LCase(Mid$(Trim(e),2))
Next
SenteceCase = Mid$(SenteceCase, 3)
End Function
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Typo mate:

SenteceCase = Mid$(SenteceCase, 3)

Missing an n in both instances :).
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Typo mate:

SenteceCase = Mid$(SenteceCase, 3)

Missing an n in both instances :).
Thanks
Rich (BB code):
Function SentenceCase(txt As String) As String
Dim e
For Each e In Split(txt, ".")
    SentenceCase = SentenceCase & ". " & UCase(Left$(Trim(e),1)) & _
        LCase(Mid$(Trim(e),2))
Next
SentenceCase = Mid$(SentenceCase, 3)
End Function
 

ChrisHT

New Member
Joined
Dec 2, 2008
Messages
2
Blade Hunter - that macro worked like a charm. Thank you! You've made my job so much easier and less tedious. Thanks for the other posts, too. The other suggestions also worked, but the macro was the best for what I need to do.
 

KOWALSKI_Dariusz

New Member
Joined
Dec 27, 2016
Messages
1
A UDF would mean you would have to enter a formula into each cell. It would be a little shorter than the above but still the same effort really.

Would you not be better writing a simple sub that does it for each cell in the selected range?

Something like (Freehand so there may be errors)

Code:
Sub CapsFirstLetter()
Dim Cell As Variant
For Each Cell In Selection
    Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Right(Cell.Text, Len(Cell.Text) - 1))
Next
End Sub
Save that to your personal workbook, assign it to a button or shortcut key, select your range and hit the button or shortcut key.
Works graeat, but when I used on a table with some empty fields started to stop cos of bad lenght. So I added condition now works even with empty fields

Code:
Sub CapsFirstLetter()

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

10

Next
End Sub
 

Forum statistics

Threads
1,085,990
Messages
5,387,136
Members
402,045
Latest member
Hidalgo

Some videos you may like

This Week's Hot Topics

Top