Cap everyword in each cell of a column - macro issue

[skyport]

Need help with this:

I want to cap the first letter of every word within a cell, NOT just the first word.

=proper(A) doesn't work for me because if a word has an 's at the end such as Physician's, it will also cap that s so it ends up as Physician'S.

The following program works great for me with one problem. It caps each word correctly but I have to hit escape to get it to stop working and go on to other functions otherwise cannot stop it from continuing on after it does the job.

Can someone help me find the brakes to make it stop spinning?

Sub ToProperA()
Dim a As Range
For Each a In Selection
a.Value = StrConv(a.Value, vbProperCase)
Next a
End Sub

 

[hiker95]

skyport,

There are so many different ways to accomplish the same task.

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[skyport]

Hiker, you have certainly done more than I could have hoped for already. If you are up for one last small related challenge, let me know if this is possible: consider on the current existing program it accomplishes perfectly all that we have covered. However, when there is data that is involved where some titles to types of doctors are already in caps, such as M.D., D.C. PA or even registered nurse - RN and they should all stay that way, the program of course treats the doctors title as a complete word and in that case results in changing the titles as follows: RN-Rn , PA-Pa, MS-Ms however, with both D.C and M.D. it keeps both letters capped probably because of the periods. I can't use a period with the RN,PA or MS as it would be considered improper in those titles. Is there some way that the program could recognize when ever there is an existing two or more capped letters in a row, to leave them as is and still have the program otherwise function exactly as it is doing? I have a way to go back and manually clean the situation up but I thought it would be spectacular achievement to have the program cover this if it's possible and if you wish to take the time for a bit more on this case. If we have already gone too far on this one with your kind help and all you that you have already done and need to bring it to an end, that would be understandable.

 

[hiker95]

skyport,

We have now gone thru 4 versions of the macro.

I have fax exceeded the normal amount of time I allocate for solving problems/requests from web sites like MrExcel.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[skyport]

Bump