Last 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.
Lower 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.
|
1 2 3 |
Sub Change_Case() frmChangeCase.Show End Sub |
Code for the form:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 |
Private CaseChange As Long 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
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?
Ron –
Oh, my. Embarrassed. I’ll fix the form. Thanks.
…mrt
Rob(not Ron) –
Embarrassed still, but spelling fixed. Form fixed. Can I blame my wife? She was looking over my shoulder.
…mrt (thanks again)
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)
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!
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
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.
Try Change case in cells addon from below site.
http://exshail.web.officelive.com/ComAddins.aspx
names like mr mcritchie will be Mr McRitchie for Sentence & Proper Case.
i & i’m will be I & I’m for sentence case. With this you can also change case of full contents or part of textwords in a cell.
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
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 …
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.
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.
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.
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
Hi all
I use a few case macros in this Excel article
http://msdn.microsoft.com/en-us/library/gg469862.aspx
Have a nice day
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.
Here is my version of Title Case, which utilizes two modified versions of Excel’s PROPER function: