Trying to make a nice Title Case

[jomili]

I'm using a macro I found at https://excelribbon.tips.net/T010560...ain_Words.html, to create a macro that will let me apply a good Title Case to selected cells. I'm trying to use Peter Atherton's solution from the bottom of the page (code shown below). The macro works well, with one exception, which I've since found out is a feature of the PROPER function, to wit, letters following apostrophe's are capitalized. The idea is that "o'leary" should convert to "O'Leary", but the problem is "Jim's" shouldn't convert to "Jim'S". I've looked and found a possible solution, the formula

Please Login or Register  to view this content.

, but I'm not sure how to plumb that into this function without losing all the other functionality I want to keep. Any help on this would be greatly appreciated.

Please Login or Register  to view this content.

[6StringJazzer]

I would add code like so (insert red code after existing line of code):

Please Login or Register  to view this content.

[kev_]

The example you gave was 'S
But are there not several more? Like: can'Nt, I'Ve, he'D etc

Other than when it follows an unaccompanied O like O'Shannon, when would any letter following an apostrophe be capitalised?

My example(O'Shannon) conflicts with the 'S rule
- if all 'S converted to 's, then further code required to reinstate all of the O'Shannons etc to upper case

[jomili]

Kev,
Your objections are mine as well. it can't be just S, 'cause then we'd have "Don'T", or "You'Ve". Seems like it might be a placement issue: if it's in the back half of the word, make it lowercase, but if it's in the front half it should be capitalized.

Actual names from my database of 15K people that would cause trouble.
Triciana La'Chelle
La'Shondria Shantille
De'a Rane
Di'Jon

Words that PROPER violates:
Don'T
He'S
Wouldn'T
I'Ve
He'D
She'D

[6StringJazzer]

You can develop an algorithm that covers dictionary word contractions but you'll never have an algorithm that knows the difference between La'Shondria Shantille and De'a Rane.

[kev_]

Function LateAdjustments is now called from your original function (see below for minor amendment)
- I would rather not change the original function

This should get you close
- you can add more replacements
- but think through any consequences fully!!
- the sequence may be critical
- I have ignored your list of difficulat names most will be correct already
- De'a Rane Replace "De'A " with De'a " (note that the final character is a space)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jomili]

Kev,
Sorry it took me so long to get back. I was off work Friday and the weekend, so just getting back. I tried your solution and it rocks! As 6String Jazzer pointed out, I'll never have a 100% solution, but your methodology brings me really close. Plus, it allows me flexibility to add more criteria if needed. the only one I've added so far is Re as in "They're".

Please Login or Register  to view this content.

Thank you so much!

I've added my full Formatting macro below in case anyone else wants to use it. Any tips on making it more compact would be appreciated. BTW, Speedon and Speedoff are just separate subs I've made for turning calculation, screenupdating, etc. off and on.

Please Login or Register  to view this content.