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 = ucase(left(tempString,1))&right(tempString,len(tempString)-1) 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 |