Capitalise characters after full stop

B

bauer24

New Member
Joined
Jan 12, 2009
Messages
23
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Some code that I think should do the trick:

Code: 
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
 
Upvote 0
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.
 
Upvote 0
A small update to take account of 'I' on its own not capitalising correctly:

Code: 
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
 
Upvote 0
Here's a UDF that seems to do the job.
Code: 
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
 
Upvote 0
Ignore the second post of mine, it had a flaw which this should fix:

Code: 
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
 
Last edited:
Upvote 0
One last small correction:

Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Ramballah
  • Question
Huge nested IF or is there a simpler way?
Replies
4
Views
209
Ramballah
Ramballah
P
  • Solved
Advice needed on where to put code that stops duplicate entries
Replies
5
Views
322
Pacman52
P
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
424
Legacy 352679
L
G
  • Question
Help with writing VBA for data entry form
Replies
2
Views
803
GA036872
G
S
  • Question
Needed a formula to convert an address from all upper case to normal address format
Replies
4
Views
762
Stumpped
S

Forum statistics

Threads
1,215,273
Messages
6,123,987
Members
449,137
Latest member
abdahsankhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top