Access Excel capitalize first letter in a sentence

This Access Excel tutorial explains how to capitalize first letter in a sentence using VBA custom Function.

Capitalize first letter in a sentence

In Excel spreadsheet, there is a Function called Proper, which converts all the first letter of each word to capital letter.

Example  Using Proper Function
he is a boy. she is a girl. He Is A Boy. She Is A Girl.
he is a boy. his name is John. mary is a girl. He Is A Boy. His Name Is John. Mary Is A Girl.
Mr.  smith is happy Mr.  Smith Is Happy

Unfortunately, there is no Function to convert the first letter to capital letter in each sentence.  I guess it is because there is no clear definition of a “sentence”.

In my definition, a sentence should be separated by a full stop, any letter after a full stop should be capitalized. In this post, I will create a custom VBA Function to capitalize the first letter in a sentence.

Custom VBA Function – Capitalize first letter in a sentence

This custom Function can be used in Access and Excel but for Access you may need to add some exceptional handling for Null.

I make use of ASC Function to check if the first character after a full stop is a letter (which has Dec value between 97 to 122). The Macro ignores any character after full stop and will find the first letter to capitalize.

Public Function wCapLetter(myString)
    tempString = myString
    If InStr(myString, ".") > 0 Then
        For i = 1 To Len(myString)
            If Mid(myString, i, 1) = "." Then
                For j = i + 1 To Len(myString)
                    If Asc(LCase(Mid(myString, j, 1))) >= 97 And Asc(LCase(Mid(myString, j, 1))) <= 122 Then
                        tempString = Left(tempString, j - 1) & UCase(Mid(tempString, j, 1)) & Right(tempString, Len(myString) - j)
                        Exit For
                    End If
                Next j
            End If
        Next i
    End If
    wCapLetter = tempString
End Function

Syntax of Custom Function

wCapLetter(text)

In this Function wCapLetter, the only argument is the text you need to capitalize the first letter.

Example of Custom Function

Assume that “Example” is in Cell A1, and other examples are in A3 A4.

Example Formula Result
he is a boy. she is a girl. =wCapLetter(A2) he is a boy. She is a girl.
he is a boy. his name is John. mary is a girl. =wCapLetter(A3) he is a boy. His name is John. Mary is a girl.
Mr.  smith is happy =wCapLetter(A4) Mr.  Smith is happy

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *