Excel VBA macro to convert the case of the first letter in a sentence

Submitted by ravisagar on Mon, 11/13/2017 - 17:22

Let us say you have a some sentences in your excel sheet in one or two cells. Now if you are very particular about punctuation like me then you may want to change the case of all the words starting in a sentence. Let us say you have some sentences like below in various cells.

A1: ravi is very nice. he likes writing on his blog.
A2: he is a good boy. he like to walk in the par. he like good food.

The sentences are ending with a full stop and the next sentence is starting with a lower case first letter in the word. The following macro can fix that :) Just highlight the cell where you want to run the macro.


Sub SentenceCase()
Dim v As Variant
Dim s As String
Dim j As Long
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
s = cell.Text
v = Split(s, ".")
For j = 0 To UBound(v)
s = Application.Trim(v(j))
s = StrConv(s, vbLowerCase)
s = UCase(Left$(s, 1)) & Mid$(s, 2)
v(j) = s
Next
cell = Application.Trim(Join(v, ". "))
End If
Next
End Sub

After running the macro the sentences will be converted to the following.

A1: Ravi is very nice. He likes writing on his blog.
A2: He is a good boy. He like to walk in the par. He like good food.

Cool isn't it? It is not the best ever VBA code ever written but it gets the job done. It is much better than doing things manually. It will only work on the cells selected because we used For Each cell In Selection.Cells and it will ignore any other cell that you didn't select. The full stop in the above code acts like a delimiter. Of course more logic can be added in this macro like converting individual "i" to capital "I".

I hope this was useful. Let me know and have fun ;)

Add new comment

Want to contact me?

Life begins outside your comfort zone and that is where your courage is developed". Write to me by filling this form. For living I am an Atlassian Consultant who love both Jira and Drupal. I am the author of "Mastering JIRA" and "Mastering JIRA 7" books. I Love both JIRA and Drupal (a bit more). You can Write To Me and I will get back to you as soon as I can. Have a nice day!

Social Links

Subscribe to my mailing list.

Enter your email address: