Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro that will ignore all information within Parentheses
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro that will ignore all information within Parentheses
On Thu, 16 Feb 2012 22:30:00 +0000, PenguinDance wrote:
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro ignore formulas | Excel Discussion (Misc queries) | |||
macro - ignore row | Excel Discussion (Misc queries) | |||
Ignore macro run time error | Excel Discussion (Misc queries) | |||
Have macro ignore error | Excel Programming | |||
Easy question: Have macro ignore #N/A | Excel Programming |