JugglerJAF
Active Member
- Joined
- Feb 17, 2002
- Messages
- 297
- Office Version
- 365
- Platform
- Windows
I have some basic code which converts data (address details) in a selected range to Proper Case, i.e. the first letter of each word is capitalised.
Unfortunately, this also converts ordinal numbers such as 1st into 1St, 5th into 5Th and so on and converts anything following an apostrophe to a capital as well (e.g. "John's Court" becomes "John'S Court"
I can't simply do a find and replace on the St/Nd/Rd/Th elements of the text string as these may form part of a correctly capitalised address (e.g. I wouldn't want "High Street" to be changed back into "High street" or "The Boulevard" changed to "the Boulevard").
I had thought about replacing the specific ordinal number elements in their entirely(1St to 1st, 2Nd to 2nd and so on), but as this is address information, I could potentially have ANY number followed by the ordinal indicator, for example, my current data set has "50th Floor".
Is there any way of converting a range of text to Proper Case, but avoiding converting the text following a number or an apostrophe?
Code:
Sub Case_All_ProperCase()
For Each c In Selection
c.Value = Application.Proper(c)
Next c
End SubUnfortunately, this also converts ordinal numbers such as 1st into 1St, 5th into 5Th and so on and converts anything following an apostrophe to a capital as well (e.g. "John's Court" becomes "John'S Court"
I can't simply do a find and replace on the St/Nd/Rd/Th elements of the text string as these may form part of a correctly capitalised address (e.g. I wouldn't want "High Street" to be changed back into "High street" or "The Boulevard" changed to "the Boulevard").
I had thought about replacing the specific ordinal number elements in their entirely(1St to 1st, 2Nd to 2nd and so on), but as this is address information, I could potentially have ANY number followed by the ordinal indicator, for example, my current data set has "50th Floor".
Is there any way of converting a range of text to Proper Case, but avoiding converting the text following a number or an apostrophe?
