Try this (untested)
Option Explicit
Sub formatting()
Dim c As Range, n As Long, SplitStr As Variant
For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
SplitStr = Split(c.Value, "'")
c.Value = IIf(UBound(SplitStr) Mod 2 = 1, "''", "") & c
For n = 0 To UBound(SplitStr)
If n Mod 2 <> (UBound(SplitStr) Mod 2) Then
On Error Resume Next
c.Replace "'" & SplitStr(n) & "'", IIf(n = 0, "", "'") & WorksheetFunction.Proper(SplitStr(n)) & "'"
End If
Next
c.Value = UCase(Left(c, 1)) & Right(c, Len(c.Offset(, 1) - 1))
Next
End Sub
Bookmarks