Sentence Case

[alexfooty]

If I have a cell which contains multiple sentences in lower, upper or proper, is it possible using VBA to convert the sentences to Sentence Case, where each sentence begins with a capital letter?
The capital letter should follow a full stop, question mark or exclamation mark.
Any help would be greatly appreciated. Please bear in mind that I am a relative newbie when it comes to VBA.

 

[Peter_SSs]

there might be just one cell or several cells to convert.

This part is not impossible. For column A it could be done with this simple change

a = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(1)).Value

the cell (cells) could be anywhere on the spreadsheet!

This is more problematic. I would want to know more about the sheet. For example ..

1. Are there formulas on the sheet that need to be preserved?
2. Approximately the maximum number of rows and columns of data there might be?
3. Are there cells on the sheet that you don't want changed in this way?
   If so, how would we identify which ones need processing and which ones do not?

Could be more questions following depending on those answers to start with.

 

[alexfooty]

What I originally had in mind was just selecting any cell, or range of cells, then running the macro to convert the text in those cells.
Some cells have formulas but they would not be the cells selected.
Obviously this is proving to be far more complex than I had imagined so please say if you think I'm asking for too much.
If I said it could be any cell in the range A1 to Z40, is that a non-starter or is it possible?

 

[Peter_SSs]

If I said it could be any cell in the range A1 to Z40, is that a non-starter or is it possible?

That would be easy if it was okay to process every cell in the range. That would destroy any formulas in that range though.

If you wanted to select the cells to process, then that is also easy, though the code will be slower. However, if you are not selecting huge ranges, it will probably be fine - try. Select the cells that you want the code to act on then run the macro.

Sub Sentence_Case_v2()
  Dim RX As Object, itm As Object
  Dim cell As Range
  Dim s As String
  
  Const Patt1 As String = "(^|\.|\!|\?)( *)([a-z])"
  Const Patt2 As String = "\bi\b"
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  
  Application.ScreenUpdating = False
  For Each cell In Selection
    s = LCase(cell.Text)
    RX.Pattern = Patt1
    For Each itm In RX.Execute(s)
      Mid(s, itm.firstindex + 1, itm.Length) = UCase(itm)
    Next itm
    RX.Pattern = Patt2
    s = RX.Replace(s, "I")
    cell.Value = s
  Next cell
  Application.ScreenUpdating = True
End Sub

 

[alexfooty]

Peter - that is absolutely perfect. What a great site this is. You never fail me. Thank you so much for your time and effort

 

[Peter_SSs]

Cheers. Glad we seem to have got there in the end.