I'm hoping to get assistance writing a macro that can change the case of text in selected cells. Ideally, the macro will be able to:
This has the limitation of only being able to capitalize the first word of each sentence / cannot make exceptions.
Can a more skilled VBAer please help me adapt the code to meet the criteria described above? Thank you so much for any assistance as always.
- Capitalize the first letter of words in a selected range, BUT ALSO
- Have the flexibility to exclude certain words (like articles and prepositions, such as "of", "on", "at", "the", and other acronyms such as "IBM") which can easily be added to the code)
- To add additional complexity, if the prepositions or selected words appear at the beginning of the text, than they should be ignored / remain capitalized - "e.g. The Man Went to the Store"
So far, I've put together the following simple construct:
Code:
Sub Proper_Case()
Dim rng As Range, cell As Range
Set rng = Selection
' Loop to cycle through each cell in the specified range.
For Each x In rng
x.Value = Application.Proper(x.Value)
Next
End Sub
This has the limitation of only being able to capitalize the first word of each sentence / cannot make exceptions.
Can a more skilled VBAer please help me adapt the code to meet the criteria described above? Thank you so much for any assistance as always.