Convert text into Sentence Case text

[pedy]

Hi

I found this macro that will convert text into Sentence Case text:

Sub Sentence_Case()
For Each Cell In ActiveSheet.UsedRange. _
SpecialCells(xlTextValues)
s = Cell.Value
Start = True
For I = 1 To Len(s)
ch = Mid(s, I, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, I, 1) = ch
Next
Cell.Value = s
Next
End Sub

My questions are:

1 - How would I go about applying it to specific columns (F-J for instance),
2 - How would I apply the Capital "I" rule ?

I appreciate any help

Pedy

 

[schielrn]

For just columns F:J you could use:

Sub Sentence_Case()
For Each Cell In ActiveSheet.Range("F:J"). _
SpecialCells(xlTextValues)
s = Cell.Value
Start = True
For I = 1 To Len(s)
ch = Mid(s, I, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, I, 1) = ch
Next
Cell.Value = s
Next
End Sub

Do you mean if it is a lowercase i in a sentence it is not making it uppercase?

 

[pedy]

Wow that was fast. Thanks !

Correct, the "i" is remainning lower case...

Pedy

 

[schielrn]

Maybe try and see what this does:

Sub Sentence_Case()
For Each Cell In ActiveSheet.Range("F:J"). _
SpecialCells(xlTextValues)
s = Cell.Value
Start = True
For I = 1 To Len(s)
ch = Mid(s, I, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, I, 1) = ch
Next
Cell.Value = s
Next
With ActiveSheet.Range("F:J")
    .Replace What:=" i ", Replacement:=" I ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False
    .Replace What:=" i~?", Replacement:=" I?", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False
End With
End Sub

Hope that helps. Also I assume you know that the code does not work after an exclamation point, as there is nothing in the code to denote that as the end of a sentence?

 

[pedy]

Yup ! That seems to work just fine.

Thanks again !

Pedy

 

[pedy]

hi again,

I have a header that I want to keep in upper case. How could I include something that would change it back to upper case in Range(A1:ZZ1)?

 

[schielrn]

At the end of the code put:

Range("A1:ZZ1").value = ucase(Range("A1:ZZ1").value)

Hope that helps.