Sentence Case

alexfooty

Board Regular
Joined
Dec 30, 2018
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,579
Office Version
  1. 365
Platform
  1. Windows
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)

VBA Code:
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
AB
1if 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.
2one. two! three? fourOne. Two! Three? Four
3ONE. TWO! THREE? FOUROne. Two! Three? Four
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=SentenceCase(A1)
 

alexfooty

Board Regular
Joined
Dec 30, 2018
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Dec 30, 2018
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
Is there a way to add a toolbar button for this function?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,579
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
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".
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,579
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 (y)
This function works wonderfully.
 

alexfooty

Board Regular
Joined
Dec 30, 2018
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
Peter - getting following error
Image 1.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,579
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 30, 2018
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,240
Messages
5,641,036
Members
417,189
Latest member
koelleyath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top