Converting to sentence case using VBA

calendar_today Asked Jun 11, 2012
thumb_up 5 upvotes
history Updated April 16, 2026

Question posted 2012 · +6 upvotes

I’ve been trawling through page after page on Google and here looking for a solution to this seemingly simple request, but to no avail. Does anyone know a reliable way to convert a string to sentence case using ?

Ideally I would build it into a sub rather than a function, so it is easier to call from the GUI.

For reference, I would want:

HERE IS A LONG, UGLY UPPERCASE SENTENCE. PLEASE AMEND ME IMMEDIATELY.

to become:

Here is a long, ugly uppercase sentence. Please amend me immediately.

Converting to Title Case I found extremely simple (as there’s a built-in function for that) but converting to sentence case has proven really difficult indeed.

I have tried some of the following methods but come up with errors at every turn:

How can I get this to work?

Accepted answer +5 upvotes

You could use a RegExp to more efficiently run the parsing

Something like this

Sub Tested()
    Call ProperCaps("HERE IS A LONG, UGLY UPPERCASE SENTENCE. PLEASE AMEND ME IMMEDIATELY." & vbCrLf & "next line! now")
End Sub

Function ProperCaps(strIn As String) As String
    Dim objRegex As Object
    Dim objRegMC As Object
    Dim objRegM As Object
    Set objRegex = CreateObject("vbscript.regexp")
    strIn = LCase$(strIn)
    With objRegex
        .Global = True
        .ignoreCase = True
         .Pattern = "(^|[.?!rt]s?)([a-z])"
        If .test(strIn) Then
            Set objRegMC = .Execute(strIn)
            For Each objRegM In objRegMC
                Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM)
            Next
        End If
        MsgBox strIn
    End With
End Function

Top excel-vba Q&A (6)

+5 upvotes ranks this answer #124 out of 136 excel-vba solutions on this site .
excel-vba