Need a macro code to Cap the first letter of words in entire columns

[skyport]

I have been using a code that had been working fine but with the new data I'm using it has a flaw. Below is first the actual code I'm currently using followed by an explanation of the flaw I am now encountering.


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


The program above generally works fine. The problem in using the current code above is when a word in the sentence is actually composed of all Caps such as an MRI or CT scan at a hospital. In such a case, the MRI or CT are originally entered as all caps however when the above code is run, it does successfully and properly cap the first letter of each word in each cell however MRI consequently ends up as Mri and CT as Ct, which is the problem. If there was some way to adjust the program above or another way to cap the first letters of all the words without disturbing those such as MRI or CT and others that were already entered in as caps. I am hoping somebody has a solution for this.

 

[skyport]

Caleeco, thanks for your input. That solution works if I could allow the result to be in a different location than the inputted data, which I cant.

 

[skyport]

Hiker I am unable to supply what you are requesting per privacy restrictions of medical patient data per Gov reg. Hopefully you can still help. You have never failed me and I am grateful for that, your help and solutions.

 

[hiker95]

Hiker I am unable to supply what you are requesting per privacy restrictions of medical patient data per Gov reg. Hopefully you can still help. You have never failed me and I am grateful for that, your help and solutions.

skyport,

I have some personal projects to work on, and, will not be able to get back to you ASAP.

In the mean time:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Caleeco]

Caleeco, thanks for your input. That solution works if I could allow the result to be in a different location than the inputted data, which I cant.

Fairly straight forward to just overwrite it. This code assumed the data in the column starts in cells B3. It finds the bottom of the column (assuming there are no gaps).

For each cell It then converts the Text Case to 'PROPER' and overwrites it to the current cell.

Sub ChangeToProperCase()
Dim OriginalText As String
Dim UpdatedText As String
Dim rTextName As Range
Dim Cell As Range
Set rTextName = ActiveSheet.Range("B3", Range("B3").End(xlDown))
For Each Cell In rTextName
OriginalText = Cell.Value
UpdatedText = WorksheetFunction.Proper(OriginalText)
Cell.Value = UpdatedText
Next
End Sub

Hope that helps!

 

[skyport]

Caleeco, thanks for sticking with me on this one. The original formula was done by selecting the data to be processed so it could be selected anywhere on the page and then of course run from F8 menu for the codes. I tried that with your code but nothing happened. If it makes it easier, we could make all the data to be processed always in columns A-E . Any thoughts?

 

[Caleeco]

Caleeco, thanks for sticking with me on this one. The original formula was done by selecting the data to be processed so it could be selected anywhere on the page and then of course run from F8 menu for the codes. I tried that with your code but nothing happened. If it makes it easier, we could make all the data to be processed always in columns A-E . Any thoughts?

No problem, I like solving VBA problems (it's helping me learn it better). I can amend the code to fit your needs. Couple questions though
Are there any blank rows or cells in between your data?
Do you have a fixed data set i.e. fixed number of rows? or does it change.

 

[skyport]

I am so glad you are not giving up, thanks. No set data although it should hopefully be able to be applied to either a manually selected portion of the worksheet, which is what the previous code did, or if that becomes a problem, limit the function to just everything in columns A through E. There are no blank rows.

 

[Caleeco]

No worries man.

I think this should do what you need. Make a single cell or Multiple Cell Selection. Run the macro and it will make the first letter of all words upper case. Let me know if it works

Sub ChangeToProperCase()
Dim UpdatedText As String
Dim rTextName As Range
Dim Cell As Range


Set rTextName = Selection
For Each Cell In rTextName
    UpdatedText = WorksheetFunction.Proper(Cell.Value)
    Cell.Value = UpdatedText
Next


End Sub

 

[skyport]

Caleeco, You are definitely on the right track because the code successfully does cap all first letters of each word. However, there are two issues that hopefully you can take a look at and resolve. The first one is the code somehow also appears to capitalize letters after apostrophes, see example below:

Record of patient's treatment - before
Record Of Patient'S Treatment - after

The second issue is I still get the same problem as I had with the original previous code that I had been using for years which is explained above in the previous posting number three of this thread on page 1. Posting number five also has a perfect visual example of how original data looks versus the problems that occur when the original program was run as well as yours and then finally the way it should look. Please take a look at both postings number three and five and you will see the problems that occur with such words as: MRI, CT, EKG, which are medical words course, as well as all state abbreviations: NY, TX, NM etc.

 

[Caleeco]

Hello,

Sorry I didn't read that part of your thread before. I think the best approach would be to add an exceptions list somewhere (as you suggested) and incorporate it into the code.

I'll have to take a look tomorrow unfortunately, it's 12:30am on my side of the world! Time to catch some sleep