Capitalise characters after full stop

[bauer24]

Hi all,

I have a bizarre problem. I have a column where each row/cell contains a sentence (or two). These setnatces are currently in proper case (i.e. "They Look Like This"). Really I want the entire string to be lower case unless the character comes after a period/full stop.

Example:

"This Is How The Data Currently Looks. Which Is Wrong" would become "This is how the data currently looks. Which is wrong"

Any ideas chaps?

Ta
D

 

[Domski]

Some code that I think should do the trick:

Sub Sentence_Case()
Dim myRange As Range, mySent As String, Start As Boolean, I As Long, ch As String
For Each myRange In Selection.SpecialCells(xlTextValues)
mySent = myRange.Value
Start = True
For I = 1 To Len(mySent)
ch = Mid(mySent, 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(mySent, I, 1) = ch
Next
myRange.Value = mySent
Next
End Sub

Select the range and run the code.

Hope it helps,

Dom

 

[drsarao]

If it is a one time only requirement, you can copy-paste to MS Word, select the table & change to sentence case (Format-->Change Case...) and then copy back to excel.

 

[Domski]

A small update to take account of 'I' on its own not capitalising correctly:

Sub Sentence_Case()
Dim myRange As Range, mySent As String, Start As Boolean, I As Long, ch As String
For Each myRange In Selection.SpecialCells(xlTextValues)
mySent = myRange.Value
Start = True
For I = 1 To Len(mySent)
ch = Mid(mySent, 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 "H"
    If Start Then Start = False Else ch = LCase(ch)
Case "I"
    If Start = False And Mid(mySent, I - 1, 1) <> " " Then ch = LCase(ch)
Case "J" To "Z"
    If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(mySent, I, 1) = ch
Next
myRange.Value = mySent
Next
End Sub

Dom

 

[Norie]

Here's a UDF that seems to do the job.

Option Explicit
Function SentenceFormat(c As Range) As String
Dim arr
Dim I As Long

    arr = Split(c, ". ")
    For I = LBound(arr) To UBound(arr)
        arr(I) = Trim(arr(I))
        arr(I) = UCase(Left(arr(I), 1)) & LCase(Mid(arr(I), 2))
    Next I
 
    SentenceFormat = Join(arr, ". ")
 
End Function

 

[Domski]

Ignore the second post of mine, it had a flaw which this should fix:

Sub Sentence_Case()
Dim myRange As Range, mySent As String, Start As Boolean, I As Long, ch As String
For Each myRange In Selection.SpecialCells(xlTextValues)
mySent = myRange.Value
Start = True
For I = 1 To Len(mySent)
ch = Mid(mySent, I, 1)
Select Case ch
Case ".", "?", "!"
    Start = True
Case "a" To "z"
    If Start Then ch = UCase(ch): Start = False
Case "A" To "H"
    If Start Then Start = False Else ch = LCase(ch)
Case "I"
    If Start = False And Mid(mySent, I + 1, 1) <> " " Then ch = LCase(ch)
    If Start Then Start = False
Case "J" To "Z"
    If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(mySent, I, 1) = ch
Next
myRange.Value = mySent
Next
End Sub

Add more punctuation that you wish to start a new sentence as required.

Dom

 

[bauer24]

the people on this site never fail to amaze me.

it works a treat

Thanks you

 

[Domski]

One last small correction:

Sub Sentence_Case()
Dim myRange As Range, mySent As String, Start As Boolean, I As Long, ch As String
For Each myRange In Selection.SpecialCells(xlTextValues)
mySent = myRange.Value
Start = True
For I = 1 To Len(mySent)
ch = Mid(mySent, I, 1)
Select Case ch
Case ".", "?", "!"      'add punctuation to start sentence here
    Start = True
Case "a" To "h"
    If Start Then ch = UCase(ch): Start = False
Case "i"
    If Start Then
        ch = UCase(ch)
        Start = False
    Else
        If Mid(mySent, I - 1, 1) = " " And Mid(mySent, I + 1, 1) = " " Then ch = UCase(ch)
    End If
Case "a" To "h"
    If Start Then ch = UCase(ch): Start = False
Case "A" To "H"
    If Start Then Start = False Else ch = LCase(ch)
Case "I"
    If Start = False Then
        If Mid(mySent, I - 1, 1) <> " " Or Mid(mySent, I + 1, 1) <> " " Then ch = LCase(ch)
    Else
        Start = False
    End If
Case "J" To "Z"
    If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(mySent, I, 1) = ch
Next
myRange.Value = mySent
Next
End Sub

Dom