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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
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
Rich (BB code):
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

alexfooty

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

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

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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
Cheers. Glad we seem to have got there in the end. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,130
Messages
5,640,301
Members
417,135
Latest member
zeusmining

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