Change Case… ala’ Microsoft Word

Mac VersionLast week, more absent-minded than usual, I forgot the software I was in. I looked for the Change Case … format dialog in Excel. It’s not there, of course. It’s in Word. It’s in PowerPoint. It’s not in Excel. So I took a morning and wrote one.

Word gives you five options to change case: Sentence case, lower case, UPPER CASE, Title Case, and tOGGLE cASE. Word’s “Title Case” is a misnomer. It’s really what we think of as PROPER() case–every word capitalized. So my version has six options, including a true Title Case in accordance with the government style manual for capitalization of short words, and a Proper Case that does exactly as MSWord does in its Title Case.

Considering The Grapes of Wrath as illustrative:

  • Sentence case returns: The grapes of wrath
  • lower case returns: the grapes of wrath
  • UPPER CASE returns: THE GRAPES OF WRATH
  • Title Case returns: The Grapes of Wrath
  • tOGGLE cASE returns: tHE gRAPES OF wRATH
  • Proper Case returns: The Grapes Of Wrath. Note the captialized of.

PC verisonLower case, upper case, and proper case were easy. They are just applications of LCase(), UCase(), or Application.Worksheetfunction.Proper() to the selection contents.

Title case starts with capitalizing every word and then swapping in the lower case versions of the one, two, and three letter words, when found between two spaces. Two spaces protects against an inappropriate changeout of a leading article.

Sentence case is a recursive search for full-stop punctuation (periods, exclamation points, question marks, and colons) reading left to right. The exit condition is when there nothing more found to the right.

Toggle case adds or subtracts 32 to the ascii-code of each letter to change its case. Upper to lower, add 32. Lower to upper, subtract 32. Non-letters are unchanged. The Like operator is used to for the comparisons in both the sentence and toggle cases.

The macro module code is a one-liner. All the execution is in the form’s code.

Sub Change_Case()
   frmChangeCase.Show
End Sub

Code for the form:

Private CaseChange As Long
Option Explicit

Private Sub CommandButton1_Click()   ‘Cancel
   Unload Me
   End
End Sub

Private Sub CommandButton2_Click()   ‘OK
   Me.Hide
   Change_Case_1
End Sub

Private Sub OptionButton1_Click()   ‘Sentence case
   CaseChange = 1
End Sub

Private Sub OptionButton2_Click()   ‘lower case
   CaseChange = 2
End Sub

Private Sub OptionButton3_Click()   ‘UPPER CASE
   CaseChange = 3
End Sub

Private Sub OptionButton4_Click()   ‘Title Case
   CaseChange = 4
End Sub

Private Sub OptionButton5_Click()   ‘tOGGLE cASE
   CaseChange = 5
End Sub

Private Sub OptionButton6_Click()   ‘Proper Case
   CaseChange = 6
End Sub

Private Sub UserForm_Initialize()
   OptionButton4 = True
   CaseChange = 4
End Sub

Private Sub Change_Case_1()
‘http://www.writers.com/tips_titles.html
‘Capitalize all words in titles of publications and documents, except
‘a, an, the, at, by, for, in, of, on, to, up, and, as, but, it, or, and nor.
   Dim Cell    As Range
   Dim i       As Long
   Dim Replacement As String
   Dim SubOut  As Variant
   Dim SubIn   As Variant

   SubIn = Split(“a an the at by for in of on to up and as but it or nor”)
   SubOut = Split(“A An The At By For In Of On To Up And As But It Or Nor”)
   
   For i = LBound(SubIn) To UBound(SubIn)
      SubIn(i) = VBA.Chr$(32) & SubIn(i) & VBA.Chr$(32)
      SubOut(i) = VBA.Chr$(32) & SubOut(i) & VBA.Chr$(32)
   Next i

   For Each Cell In Selection
      If Application.WorksheetFunction.IsText(Cell.Value) Then
         Select Case CaseChange
            Case 1   ‘The grapes of wrath
               For i = 1 To Len(Cell.Text)
                  If Mid$(Cell.Text, i, 1) Like “[A-Za-z0-9]” Then
                     Replacement = UCase(Left$(Cell.Text, i)) & SentenceCase(Right$(Cell.Text, Len(Cell.Text) – i))
                     ‘ Recursive
                     Exit For
                  End If
               Next i
               Cell.Value = Replacement
            Case 2   ‘the grapes of wrath
               Cell.Value = VBA.LCase(Cell.Value)
            Case 3   ‘THE GRAPES OF WRATH
               Cell.Value = VBA.UCase(Cell.Value)
            Case 4   ‘The Grapes of Wrath
               Replacement = Application.WorksheetFunction.Proper(Cell.Value)
               For i = LBound(SubOut) To UBound(SubOut)
                  Replacement = VBA.Replace(Replacement, SubOut(i), SubIn(i), vbBinaryCompare)
               Next i
               Cell.Value = Replacement
            Case 5   ‘ tHE gRAPES oF wRATH
               Replacement = “”
               For i = 1 To Len(Cell.Text)
                  If VBA.Mid$(Cell.Text, i, 1) Like “[A-Z]” Then
                     Replacement = Replacement & VBA.Chr$(VBA.Asc(VBA.Mid$(Cell.Text, i, 1)) + 32)
                  ElseIf VBA.Mid$(Cell.Text, i, 1) Like “[a-z]” Then
                     Replacement = Replacement & VBA.Chr$(VBA.Asc(VBA.Mid$(Cell.Text, i, 1)) – 32)
                  Else
                     Replacement = Replacement & VBA.Mid$(Cell.Text, i, 1)
                  End If
               Next i
               Cell.Value = Replacement
            Case 6   ‘The Grapes Of Wrath
               Cell.Value = Application.WorksheetFunction.Proper(Cell.Value)
         End Select
      End If
   Next Cell
   Unload Me
End Sub

Private Function SentenceCase(Sentence As String) As String
   Dim i As Long, j As Long, n As Long
   Dim LeftSide As String, RightSide As String

   n = Len(Sentence)
   For i = 1 To n
      If Mid$(Sentence, i, 2) Like “[.!?:] ” Then
         LeftSide = Left$(Sentence, i + 1)
         RightSide = Right$(Sentence, n – i – 1)
         Exit For
      ElseIf i = n Then
         LeftSide = Sentence
         RightSide = vbNullString   ‘Exit condition
      End If
   Next i

   For j = 1 To Len(RightSide)
      If Mid$(RightSide, j, 1) Like “[A-Za-z0-9]” Then
         RightSide = UCase(Left$(RightSide, j)) & SentenceCase(Right$(RightSide, Len(RightSide) – j))
         ‘Recursive
         Exit For
      End If
   Next j

   SentenceCase = LeftSide & RightSide

End Function

I wanted to use the antique char|32 and char&32 to do the toggle-case change, but I couldn’t find the right syntax. Wiki says it’s not any faster these days, but readers may want to comment.

Tested on a PC and a Mac (using XL2011). The Mac does not appear to support tool-tips from the form. The Mac version looked better in Tahoma 12, the PC version in Tahoma 10. I compromised.

The form is available here.

…mrt

Posted in Uncategorized

16 thoughts on “Change Case… ala’ Microsoft Word

  1. I bought a pair of swimming goggles the other day, and nearly referenced the banking payment “googles”
    I wonder if these typos (like tOOGLE cASE) are linked to the rise of google?

  2. Rob(not Ron) –

    Embarrassed still, but spelling fixed. Form fixed. Can I blame my wife? She was looking over my shoulder.

    …mrt (thanks again)

  3. I did this 18 months ago as a stand-alone macro that pre-empts TitleCase in the Format menu.
    I elected to have a user-maintained string (in the INI file) to inhibit common words, For the first word in a sentence I ignore this rule:
    ProperTitleReference=,a,all,an,and,are,can,do,for,from,in,is,it,of,on,or,so,that,the,this,to,up,was,we,with,has,gone,

    If a source word is NOT fully lower-case, then I elect to leave it as-is, assuming that the user has a reason for emphasizing saveAs (as an example)

  4. Thanks for this, Michael. If I get time, and after I get used to our new installation of Win7 & Office 2007 at work (not this email address), I hope to see if I can use it to fix Word’s functionality – as it does not correctly do Title Case for a multiple line selection (using F3 at least), which drives me mad. Not quite what you had in mind, I guess!

  5. Hi Chris – the Sentence-case election only upper-cases the very first letter and then all letters after a full stop. Anything in the middle is left as is.

    Hi Stephen – as much as I love Excel, I hate Word, and I grew to be an accomplished user by necessity, with emphasis on “user.” Very few Word macros, so I can’t advise the changes required, except to remind that Word’s Title Case isn’t truly that. I never really got past the recorder stage of my Word existence. :roll:

    …mrt

  6. Sentence Boundary Detection (even sticking just with English) is far from trivial and as far as I’m aware has not been solved.

    Some of the usual “gotchas”
    – Period after abbreviations
    – Period within numbers, email addresses etc.
    – Gramatically ! and ? Do not always end sentences.

    There’s a lot to be said for the old fashioned (sic) idea of double spacing after any sentence.

  7. Mpemba –

    I agree with your point about abbreviations, though many abbreviations are followed by a Proper Name that should be capitalized.

    There are no period/decimal/dot-space pairs within properly rendered numbers or email addresses.

    Question marks and exclamation points that do not end sentences are followed by a quotation mark, not a space. I also agree those situations may fail to be correctly resolved. The purists should object to my putting a colon in as a full stop. Those that do can edit it out. ;-)

    You and I are about the only ones left who use two spaces following a sentence. Old habit, dying hard. My typing teacher would be proud.

    …mrt

  8. Michael

    Don’t get me wrong – I love the code.

    It’s just that I’ve encountered these questions in the past – largely these days from trying to automatically “tidy up” OCR’d documents. It’s a science in itself and seriously, has not been solved.
    OK, if your documents will always conform to some modern style manual, it makes things a lot easier.

    “Question marks and exclamation points that do not end sentences are followed by a quotation mark, not a space.”
    That is factually incorrect, at least in the English version of English. OK, it’s not so common in modern times but you’ll find both used mid-sentence in 20th century literature

    While Wikipaedi is not the oracle:
    http://en.wikipedia.org/wiki/Exclamation_mark
    “and often marks the end of a sentence” … not the word “often”

    “Exclamation points can also be placed mid-sentence with a function similar to a comma”

    Question marks that don’t end a sentence are rarer but not completlety illegal.
    http://grammar.ccc.commnet.edu/grammar/marks/question.htm

    [“Sometimes a question will actually end with a series of brief questions. When that happens, especially when the brief questions are more or less follow-up questions to the main question, each of the little questions can begin with a lowercase letter and end with a question mark.
    Who is responsible for executing the plan? the coach? the coaching staff? the players?”]

    Again – this is just a web site but it certainly occurs in 20th centurt literature.

    And if I decide to name my film “Who?” (including the question mark) – Just as Rogers and Hammerstein named “Oklahoma!” :o)

    M …

  9. You and I are about the only ones left who use two spaces following a sentence.

    In the electronic age it takes a second to replace all occurances of “. ” in a document with “. “.
    So much so it seems such a non-issue. It could take hours to decide where to put them back.

  10. I too do text-cleansing. I do the best (automated) I can, but draw the line at fixing very weird writing errors. Much like automated processing of VBA code (nesting, removing deadwood etc.) I start by assuming that the code is syntactically correct, that it is a clean Debug, Compile.
    So with “sentencing” applications, If the user has written a random set of characters, all bets are off. If the English is “pure” it works 100%. Then there’s a scale of success from one end to the other.

  11. Chris

    Life would be so much simpler if “English” had a clear, unambiguous, end of sentence marker.
    Yes, in “text cleansing” little things like the apostrophe and single quotation mark sharing the same glyph just make things that bit harder. Most of what I deal with are old (19th and 20th century) texts which were gramatically correct when they were written.

    It’s almost impossible to tidy up the OCRs without a lot of user input. You cannot assume a ! ends a sentence.

    But this was not menat to “diss” the code – just point out that no automatic rule will ever be perfect when faced with “dual purpose” glyphs.

  12. M –

    We put them in, the browsers take them out ;-( Useless white space.

    I didn’t ever consider the approaches a non-technical writer could take with the language, which is Exhibit “A” for why I don’t have the imagination to be one.

    All my writing since undergraduate days has been IAW one style manual or the other. Why doesn’t everyone write that way? ;-)

    …mrt

  13. A couple of points of possible interest:
    In Office 2007 onwards (in Word and PowerPoit at least) they more correctly label the old option “Title Case” in the drop down on the Ribbon as “Capitalize Each Word” to appease the (completely correct) pedants of the definition of Title Case.

    In Word, using the shortcut Shift+F3 will cycle round different cases, but in a very specific way:
    I it detects a sentence (based on presence of a punctuation mark such as a period, and possibly some more clever things too), you get upper, lower, sentence case. This applies to single sentences with a final full stop, or multiple sentences with stops in the middle (with or without a final one).
    If no sentence is detected, the cycle is upper, lower, every word – this is suitable for things like headings, titles, bullet points perhaps.

    In PowerPoint the same shortcut Shift+F3 always cycles upper, lower and sentence case, regardless of punctuation, for selected text, or whole text boxes or placeholders. There is no shortcut to get Title Case (or capitalize every word), as far as I am aware.

    I propose an additional option: Apple case. This should find every word which begins with the letter “i” and capitalise only the second letter of the word as this is iNvariably the way those iDiots in marketing seem to like iT.

Leave a Reply

Your email address will not be published. Required fields are marked *