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

 

[Blade Hunter]

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)

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.

 

[Aladin Akyurek]

Shorter...

=REPLACE(A1,1,1,UPPER(LEFT(A1)))

 

[stanleydgromjr]

ChrisHT

Your formula works for me.

Excel Workbook
	A	B
1	this is a test	This is a test
2	chris has a question	Chris has a question
Sheet1

Have a great day,
Stan

 

[jindon]

UDF

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]

Typo mate:

SenteceCase = Mid$(SenteceCase, 3)

Missing an n in both instances .

 

[jindon]

Typo mate:

SenteceCase = Mid$(SenteceCase, 3)

Missing an n in both instances .

Thanks

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]

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.

 

[Blade Hunter]

Happy to help mate

 

[KOWALSKI_Dariusz]

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)

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

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