Modifying Proper Capitalization

My Excel Toolbox includes the following function to return Text converted to title case (for example, Now Is the Time for All Good Men to Come to the Aid of Their Country):
=Title(Text)
The rules for title case are imprecise (see http://capitalizemytitle.com), so the results of this function should be carefully reviewed. Here is the array of proper case words that are converted to lower case by the Title function:
Split("A An And As At But By For From In Nor Of On Or So The To Up With")
For best results, all words in Text should be lower case except words that must be capitalized like IBM or iPhone or McDonald's. For example, consider the following Text:
my "old-lady" isn't at McDonald's with her 2nd 'Apple-PC' (a.k.a. iPhone)
=Title(Text) returns the following result:
My "Old-Lady" Isn't at McDonald's with Her 2nd 'Apple-PC' (A.K.A. iPhone)
My Excel Toolbox also includes the ProperX and ProperY functions to convert to proper case like Excel's PROPER function except for letters after digit or apostrophe (ProperX) or words with an initial apostrophe (ProperY). For the example Text above:
=PROPER(Text) returns the following result:
My "Old-Lady" Isn'T At Mcdonald'S With Her 2Nd 'Apple-Pc' (A.K.A. Iphone)
=ProperX(Text) returns the following result:
My "Old-Lady" Isn't At Mcdonald's With Her 2nd 'apple-Pc' (A.K.A. Iphone)
=ProperY(Text) returns the following result:
My "Old-Lady" Isn't At Mcdonald's With Her 2nd 'Apple-Pc' (A.K.A. Iphone)
=StrConv(Text,vbProperCase) in VBA returns the following result:
My "old-lady" Isn't At Mcdonald's With Her 2nd 'apple-pc' (a.k.a. Iphone)
My Excel Toolbox also includes the TextTitleCase macro to convert all text constants in Selection to title case. Similarly, these macros are available: TextLowerCase, TextUpperCase, TextProperCase, TextSmallCaps, TextToNumber, TextFromNumber, TextTrim, and TextClean. All support Undo (Ctrl+Z).
See https://sites.google.com/view/MyExcelToolbox/

Hi all,

Beside the sophisticated and professional solutions you are giving here for this issue, I got a simple work around:

- Copy the text to the MS word.
- Highlight all the words you need to modify their case
- Press the CAP Toggle "Shift+F3", you will notice that their case is changing from (ALL CAPS) to (all small), press again and it's changed to (First Letter), and so on, till you are satisfied with the result.
- Copy them back to your Excel sheet.

Voila :)

Small problem: the macro won't detect the words if right before a punctuation mark ("and,", "and.", "and!"...)

Allen, you sure are in love with Application.Volatile and Application.Worksheetfunction! Both of these are unnecessary and will only slow down your calculations.

Application.Volatile tells Excel to recalculate the cell anytime a recalculation is done, even if the dependant cells don't change. In other words, if Cell B1 is =MyProper(A1), by default B1 will only recalculate when A1 changes; add Application.Volatile and B1 will change when *any* cell changes! (Application.Volatile is useful for when you have a function that includes values that are affected by something which can change, but is not a dependant cell: Range.Offset, Date, etc).

Secondly, there is a built-in VBA function that replicates the Excel Substitute function: Replace. It is always faster to use a VBA function within VBA than to cross the “VBA/worksheet barrier”. As a bonus, you can force it to use vbTextCompare so you can avoid all the extra StrConv calls.

Lastly, if you are going to add spaces around every use of vExclude, then just add the spaces to the array. A better version of the first code follows.

Function MyProper(str As String) As String
Dim vExclude As Variant
Dim i As Integer

vExclude = Array(" a ", " an ", " in ", " and ", _
" the ", " with ", " is ", " at ")

str = StrConv(str, vbProperCase)

For i = LBound(vExclude) To UBound(vExclude)
str = Replace(str, vExclude(i), vExclude(i), _
Compare:=vbTextCompare)
Next

MyProper = str

End Function

See also tip 10560 for further discussion & a different macro.

Here's an expanded list of words which s/n be capitalized for Title case:

"a", "ago", "an", "and", "as", "at",
"atop", "but", "by","for", "from", "in", "into", "near", "nor", "on", "onto",
"off", "over", "of", "or", "out", "the",
"this", "to", "up", "with"