If I recall correctly the issue comes with a hyphenated name. People-power with a capital for the first letter and a lowercase for the last was the desired format, but the text produced by people came with the variation People-Power. It is simply not a good idea to ask a person to verify this when VBA will so merrily do it for you:
Sub arrayManip(thisCell) ' clear out all data Erase strArray txt = "" strTest = "" 'set default case lCaseOn = False ' string into an array using a " " separator strTest = WorksheetFunction.Proper(thisCell) MsgBox "string Proper is " & strTest strTest = Replace(strTest, "-", " - ") strTest = Replace(strTest, "‘", " ‘ ") strArray = Split(strTest, " ") ' itterate through array looking to format text For i = LBound(strArray) To UBound(strArray) If strArray(i) = "-" Then lCaseOn = True ElseIf strArray(i) = "‘" Then lCaseOn = True End If If lCaseOn Then strArray(i) = LCase(strArray(i)) lCaseOn = False End If ' loop through the array and build up a text string for output to cell Call cleanTxt(strArray, txt) MsgBox "output will be " & txt End Sub Function cleanTxt(strArray, txt) ' string the array back together and remove the spaces For i = LBound(strArray) To UBound(strArray) txt = txt & strArray(i) & " " Next i txt = Trim(Replace(txt, " - ", "-")) txt = Trim(Replace(txt, " ‘ ", "‘")) MsgBox "the string now looks like this " & txt End Function |