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 ;)
