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]

Not sure if you want to actually change the data, or get the corrected results in another cell/column? Either is possible. here is one way using a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function SentenceCase(s As String) As String
  Dim RX As Object, itm As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|\.|\!|\?)( *)([a-z])"
  s = LCase(s)
  For Each itm In RX.Execute(s)
    Mid(s, itm.firstindex + 1, itm.Length) = UCase(itm)
  Next itm
  SentenceCase = s
End Function

alexfooty 1.xlsm
	A	B
1	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.	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.
2	one. two! three? four	One. Two! Three? Four
3	ONE. TWO! THREE? FOUR	One. Two! Three? Four
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=SentenceCase(A1)

 

[alexfooty]

Hi Peter - many thanks for your quick reply. This function works wonderfully. Is it possible to change the data in the actual cell? (also is it possible to capitalise the letter "i" when it appears on its own?)

 

[alexfooty]

Is there a way to add a toolbar button for this function?

 

[Peter_SSs]

Is it possible to change the data in the actual cell? (also is it possible to capitalise the letter "i" when it appears on its own?)

Test with a copy of your data. I have assumed data is in column A, starting at row 2.

Sub Sentence_Case()
  Dim RX As Object, itm As Object
  Dim a As Variant
  Dim s As String
  Dim i As Long
 
  Const Patt1 As String = "(^|\.|\!|\?)( *)([a-z])"
  Const Patt2 As String = "\bi\b"
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
 
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = LCase(a(i, 1))
    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")
    a(i, 1) = s
  Next i
  Range("A2").Resize(UBound(a)).Value = a
End Sub

Is there a way to add a toolbar button for this function macro?

Google "excel add macro to quick access toolbar"

 

[Rick Rothstein]

I don't know if this could be a problem for the OP or not, but one thing Peter's code does not handle correctly (nor would anyone else's code be able to handle correctly without some sort of specialized "dictionary") is abbreviations such as "Mr.", "Mrs.", "Dr.", "sq. ft.", "Ave.", "Feb.", "Mar." (these last four will also upper case the first letter of any word that follows them), as well as a whole host of other such abbreviations and as well as certain names such as "MacArthur" and "McDonald's".

 

[Peter_SSs]

one thing Peter's code does handle correctly

I know you meant "does not handle correctly"

as well as certain names such as "MacArthur" and "McDonald's".

Not only certain names - any names or proper nouns that are not the first word after one of the punctuation marks listed.


Still OP seems happy so far

This function works wonderfully.

 

[alexfooty]

Peter - getting following error

 

[Peter_SSs]

Peter - getting following error

That tells me where the error is, but not what the error is. Always best to provide both bits of information.

In this case my guess is Run-time error 13 Type mismatch?

If so, that is because there is only one cell to process, not an array of cells. Is that the case? If so, is that because
- you are only testing on one cell or
- when using this code there will only be one cell or
- when using the code there might only be one cell?

If I have guessed the wrong error or the wrong cause can you provide some sample data with XL2BB

 

[alexfooty]

It is Run-time error 13
When I tested in several cells in col A everything worked fine
Unfortunately there might be just one cell or several cells to convert. Also, again, unfortunately the cell (cells) could be anywhere on the spreadsheet!
I'm assuming this is an impossible task. Hope I'm wrong