I have a column of cells that contains a short bio on a person. For
example:
(NAME: CHAD SMITH) HE IS A PERSONAL COMPUTER (PC) USER WHO ENJOYS
PLAYING GAMES ON HIS SUPER NINTENDO (SNES). HE ALSO ENJOYS VOLLEYBALL
(VBALL) AND BASKETBALL. (WRITTEN BY: JOHN JONES).
I would like a macro that puts all the NON parentheses text into
sentence case (NOT PROPER CASE), but leave the text within the
parentheses unchanged.
I found and successfully ran this Macro which puts everything into
sentence case:
Sub SentenceCase()
For Each cell In Selection.Cells
s = cell.Value
Start = True
For i = 1 To Len(s)
ch = Mid(s, i, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, i, 1) = ch
Next
cell.Value = s
Next
End Sub
It works great, but I want everything within the parentheses to be
ignored. Anyway I can do that? Thanks in advance for all your help!
If each bio, consisting of several sentences, is within a single cell, then the following should accomplish what you want.
By the way, it is good practice, and makes understanding and debugging your code much much simpler, to explicitly declare all of your variables. This can be "forced" easily with the VBE by selecting Tools/Options/Editor "Require Variable Declaration". This will place Option Explicit at the start of any new module you open.
I used Regular Expressions to develop the various patterns needed to preserve the Parentheses text. However, if you have parentheses text that contains a dot followed by a lower case letter, this macro will Ucase that letter. IOW,
(NAME: chad . smith) would become (NAME: chad. Smith). Since I thought this to be unlikely, given your examples, I did not code for this sort of problem, but it could be done if necessary, just more complex. The various regex patterns used are all listed in the variable declaration segment.
In the macro below, the strings are assumed to occupy Column A; the results are put on the same row in Column B.
One issue is that, if you have proper nouns within the non-parentheses text, you have not provided any information to allow them to be capitalized, so they will not be. If that is an issue, you will need to develop a list of proper nouns you will be using, and do a find/replace (which could be included within the code).
======================================
Option Explicit
Sub SentenceCase()
Dim rSrc As Range, c As Range
Dim re As Object, mc As Object, m As Object
Dim sRes As String
Const sPatSplit As String = "(\([^)]+\))?([^(]+(?=\(|$))"
Const sPatSpcB4Dot As String = "\s*\."
Const sPatStartFirstSentence As String = "^(?:\([^)]+\))?\s*[a-z]"
Const sPatLtrAftrDot As String = "\.[ \t]+\w"
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.MultiLine = False
End With
'cycle through strings in column A
For Each c In rSrc
sRes = ""
re.Pattern = sPatSplit
'Lcase everything not in parentheses
If re.test(c.Text) = True Then
Set mc = re.Execute(c.Text)
For Each m In mc
sRes = sRes & " " & m.submatches(0) _
& " " & LCase(m.submatches(1))
Next m
End If
'remove extra spaces
re.Pattern = sPatSpcB4Dot
sRes = re.Replace(WorksheetFunction.Trim(sRes), ".")
'capitalize first sentence start
re.Pattern = sPatStartFirstSentence
Set mc = re.Execute(sRes)
With mc(0)
Mid(sRes, .firstindex + .Length, 1) = UCase(Mid(sRes, .firstindex + .Length, 1))
End With
'capitalize first character after dot
re.Pattern = sPatLtrAftrDot
Set mc = re.Execute(sRes)
For Each m In mc
With m
Mid(sRes, .firstindex + .Length, 1) = UCase(Mid(sRes, .firstindex + .Length, 1))
End With
Next m
'write results
c.Offset(columnoffset:=1).Value = sRes
Next c
End Sub
==============================================
With your example, the following is the result:
(NAME: CHAD SMITH) He is a personal computer (PC) user who enjoys
playing games on his super nintendo (SNES). He also enjoys volleyball
(VBALL) and basketball. (WRITTEN BY: JOHN JONES).
