Thanks for coming here, I hope you are enjoying learning here, I have also written some books in case you want to learn a bit more :)
If you need my help with Drupal, Linux, Jira, Scripting, Automation or want to contact me then raise a ticket for me please :) and I will get back to you, promise

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

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.

  1. Sub SentenceCase()
  2. Dim v As Variant
  3. Dim s As String
  4. Dim j As Long
  5. Dim cell As Range
  6. For Each cell In Selection.Cells
  7. If cell.HasFormula = False Then
  8. s = cell.Text
  9. v = Split(s, ".")
  10. For j = 0 To UBound(v)
  11. s = Application.Trim(v(j))
  12. s = StrConv(s, vbLowerCase)
  13. s = UCase(Left$(s, 1)) & Mid$(s, 2)
  14. v(j) = s
  15. Next
  16. cell = Application.Trim(Join(v, ". "))
  17. End If
  18. Next
  19. 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 ;)

Similar posts

Ravi Sagar Newsletter

Honestly I hardly send out mails but I guess there is no harm in at least having a newsletter. I will only send newsletter when I have something important to share or an update. People are busy and no one has time to read emails these days. If you really like my content then I guess you will remember my site and come back for more.

Want to contact me?