need help with minor adjustment to a macro

[skyport]

The current macro I am using very nicely caps all words in each cell in a column, which is the main idea. The problem is I am now running into the following issue:

Consider the following sentence in any cell in column A:

claimant's recorded statement

After the macro is ran it would obviously cap each word as follows:


Claimant's Recorded Statement

However, if the original sentence contains something with pre-existing multiple caps consecutively such as the 3 examples below where the issue pertains to MRI and QME and MI :

review of MRI Of thoracic spine
MRI Of thoracic spine
panel QME Ml-104 supplemental report

The current macro I am using would translate those 3 sentences as follows:

Review Of Mri Of Thoracic Spine
Mri Of Thoracic Spine
Panel Qme Mi-104 Supplemental Report

Instead of as it needs to be like the following:

Review Of MRI Of Thoracic Spine
MRI Of Thoracic Spine
Panel QME Ml-104 Supplemental Report

Conclusion: Because such situations only occur with such combos as MRI, QME or MI entered into the cell as caps already before the macro is even used, I was hoping there would be a way for the program to do what it currently does yet skip over any current letter already in caps and not change it OR maybe it could recognize and leave untouched or skip over any situation where a word already exists with a cap or maybe 2 caps or more already in it.

Below is the program I am using:

Sub ConvertSelectionUpper_V4()
' hiker95, 08/27/2014, ME800718
Dim c As Range, s, i As Long, s2, h As String
Application.ScreenUpdating = False
With Selection
.Value = Evaluate("=if(len(" & .Address & "),substitute(proper(" & .Address & "),"" And "","" and ""),"""")")
.Columns.AutoFit
End With
For Each c In Selection
h = ""
If InStr(c, "'") Then
s = Split(Trim(c), " ")
For i = LBound(s) To UBound(s)
If InStr(s(i), "'") Then
s2 = Split(s(i), "'")
h = h & s2(0) & "'" & LCase(s2(1)) & " "
Else
h = h + s(i) & " "
End If
Next i
If Right(h, 1) = " " Then
h = Left(h, Len(h) - 1)
End If
c = h
End If
Next c
Application.ScreenUpdating = True
End Sub

Can anyone help on this one???

 

[sericom]

The following splits by space, capitalizes the first letter and then joins with a space. You'll need to adapt to suit.

strg = "review of MRI Of thoracic spine"
strgarr = Split(strg, " ")
For j = 0 To UBound(strgarr)
    temp = strgarr(j)
    temp = UCase(Left(temp, 1)) & Mid(temp, 2)
    strgarr(j) = temp
Next
newstrg = Join(strgarr, " ")

 

[skyport]

I'm afraid I am not able to understand too much on this. Someone here on Excel made the program for me. I am not sure how you are suggesting the addition would be added into it or function on it's own. Also, not sure about the adapting

 

[sericom]

If that's all you want the macro to do, then replace what you have with this:

Sub ConvertSelectionUpper_V4()
With Selection
For Each c In Selection
strgarr = Split(c.Value, " ")
For j = 0 To UBound(strgarr)
    temp = strgarr(j)
    temp = UCase(Left(temp, 1)) & Mid(temp, 2)
    strgarr(j) = temp
Next
c.Value = Join(strgarr, " ")
Next
End With
End Sub

 

[skyport]

Sericom

You nailed it - right on target. Works perfect. Thanks so much for your help.