Sentence Case Question

[odie837]

Hi,
I have a column of data that I need to change from all capital letters into sentence case (not proper case). For example:

THIS IS WHAT I HAVE. THIS IS WHAT I HAVE.

...and this is what I want:

This is what I have. This is what I have.

I've looked online through this message board and there doesn't really seem like an easy way to do it (or even any way to do it). Manually I know I can copy the column of data into Word and have word change it into sentence case, and then repaste it back into my spreadsheet, but I would really like to write this into my macro so that it does this automatically.

Thanks! Any help is greatly apprechiated!!!


Odie

 

[Ekim]

odie,

Macro runs OK on your data with three exceptions:

1. There must be a period “.”, or a question mark “?” or an exclamation mark “!” at the end of the last sentence. In your example, the second string does not have a period at the end so the macro returned a blank cell for this string.
2. In the last string, Chris Carr is shown as chris carr (see Note at end)
3. A part number such as 2N09-3136 is returned as 2n09-3136. Similarly, 820R181G01 is returned as 820r181g01 (see Note at end)

Set-up
The macro assumes that the strings are in column A. The macro inserts a new column A and formats the strings in the new column. The original strings in upper case are now in column B. The macro ultimately deletes column B.

Put the macro and the UDF in a standard module.

Option Explicit
Sub TextConvert()
Dim c As Range

Application.ScreenUpdating = False

For Each c In Range("A1:A" & Range("A65536").End(xlUp).Row)
  If c.Value <> "" Then
    c.Value = UCase(Left(c.Text, 1)) & _
           LCase(Right(c.Text, Len(c.Text) - 1))
 
    c.Value = Application.Substitute(Application.Substitute(c.Text, " i ", " I "), _
             " i?", " I?")
  End If
Next c

Columns(1).Insert

With Range("A1")
  .Formula = "=CapFirstLetterOfSentences(B1)"
  .AutoFill Destination:=Range("A1:A" _
          & Range("B65536").End(xlUp).Row)
End With

With Columns(1)
  .Value = .Value
  .AutoFit
End With

Columns(2).Delete
Application.ScreenUpdating = True

End Sub


Function CapFirstLetterOfSentences(ByVal str As String) As String
' Daniel M.
' microsoft.public.Excel.programming
' 5 August 02

Dim aRegExp As Object, aMatch As Object, allMatches As Object
Set aRegExp = CreateObject("vbscript.regexp")
aRegExp.Pattern = "(\s*)(\w)([^.?!]*)([.?!]+)"
aRegExp.Global = True
Set allMatches = aRegExp.Execute(str)
str = ""
For Each aMatch In allMatches
    With aMatch.SubMatches
    str = str & .Item(0) & UCase(.Item(1)) & .Item(2) & .Item(3)
    End With
Next aMatch
CapFirstLetterOfSentences = str
End Function

Notes:
Patches can be added to the macro to handle specific anomalies e.g. find “chris carr” and replace with “Chris Carr”, find “820r181g01” and replace with “820R181G01” etc. Obviously, this could affect macro performance depending on the number of patches.

HTH

Mike