Change Case of Texts to Proper, Except for Prepositions

[haibiz9]

Hello the VBA experts,

I need help with a macro that will change the case of a string of texts to proper except for prepositions (e.g. and, or, about, the, etc.). Any help would be greatly appreciated.

Thanks.

 

[Tom Urtis]

Just an idea for starters, not sure if your data of interest is in one column or many columns. So to be on the safe side, avoiding helper columns if a lot of columns are involved, select any cell in that range with strings and then run this macro on a copy of your workbook:

Sub Test1()
If ActiveSheet.CodeName = "Sheet1" Or ActiveSheet.CodeName = "Sheet2" Then
Application.ScreenUpdating = False
With ActiveCell.CurrentRegion
Dim cell As Range
For Each cell In .SpecialCells(2)
cell.Value = WorksheetFunction.Proper(cell.Value)
Next cell
.Replace What:="Or ", Replacement:="or ", LookAt:=xlPart, MatchCase:=True
.Replace What:="And ", Replacement:="and ", LookAt:=xlPart, MatchCase:=True
.Replace What:="About ", Replacement:="about ", LookAt:=xlPart, MatchCase:=True
.Replace What:="The ", Replacement:="the ", LookAt:=xlPart, MatchCase:=True
End With
Application.ScreenUpdating = True
End If
End Sub

 

[haibiz9]

Urtis- Thank you for your codes and ideas. They did give me a good starting point. The sub I have written below worked. There might be a better way to write this macro, but I'm happy with what I got. Thank you again for your help. I really appreciate it.

Private Sub ProperCaseNotPrepositions_Click()


Dim TextToConvert As String
Dim TextConverted As String


TextToConvert = Trim(TextToConvert)
TextToConvert = Application.WorksheetFunction.Proper(TextToConvert)
On Error Resume Next
TextConverted = Application.WorksheetFunction.Substitute(TextToConvert, " A ", " a ")
TextConverted = Application.WorksheetFunction.Substitute(TextConverted, " About ", " about ")
TextConverted = Application.WorksheetFunction.Substitute(TextConverted, " After ", " after ")
TextConverted = Application.WorksheetFunction.Substitute(TextConverted, " In ", " in ")
TextConverted = Application.WorksheetFunction.Substitute(TextConverted, " On ", " on ")
TextConverted = Application.WorksheetFunction.Substitute(TextConverted, " Onto ", " onto ")
TextConverted = Application.WorksheetFunction.Substitute(TextConverted, " Or ", " or ")
TextConverted = Application.WorksheetFunction.Substitute(TextConverted, " Out ", " out ")


TextBox2.Text = TextConverted
TextBox2.SetFocus


End Sub