VB Macro for Proper Case to Ignore "/" & "-"

I have a Excel text document that I compile from multiple sources, and if they don't leave a "white space" before a "/", "-", or "(" the Proper Case command does not capitalize the word that immediately follows that symbol.

 Sub Proper_Case_Inner(Optional mySelection As String)
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     Dim cell As Range
     Dim rng As Range
     On Error Resume Next   'In case no cells in selection
     If mySelection = "" Then Set rng = Selection _
         Else Set rng = Range(mySelection)
     For Each cell In Intersect(rng, _
            rng.SpecialCells(xlConstants, xlTextValues))
        cell.Formula = StrConv(cell.Formula, vbProperCase)
......

Thanks for your help in advance.

jlcford

Hi JLC,

This probably isn't the greatest code, but it should do what you're asking, at least for the characters /, - and (

Sub Proper_Case_Inner(Optional mySelection As String)
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    Dim rng As Range
    On Error Resume Next   'In case no cells in selection
    If mySelection = "" Then Set rng = Selection Else Set rng = Range(mySelection)
    For Each cell In Intersect(rng, rng.SpecialCells(xlConstants, xlTextValues))
        cell.Formula = Replace(cell.Formula, "/", "/ ")
        cell.Formula = Replace(cell.Formula, "-", "- ")
        cell.Formula = Replace(cell.Formula, "(", "( ")
        cell.Formula = StrConv(cell.Formula, vbProperCase)
        cell.Formula = Replace(cell.Formula, "/ ", "/")
        cell.Formula = Replace(cell.Formula, "- ", "-")
        cell.Formula = Replace(cell.Formula, "( ", "(")
......

This will append a space after each of those symbols, then do your conversion to proper case, then remove those added spaces.

Instead of StrConv, use

WorksheetFunction.Proper

I'll be right back.. have to go smack my forehead.