How to remove caps and switch to lowercase

kerrysr · Mar 29, 2005

I have many records that are in all caps. I need to switch them to lower case with only the 1st letter capitalized. Also, these records are mixed with records that are in the proper format (only 1st letter capitalized)

How can I do this?

PA HS Teacher · Mar 29, 2005

Excel has a built in function called Propper.
=Propper(WORds wiTH some Lower case)

results in
Words With Some Lower Case

HalfAce · Mar 29, 2005

If you're doing this infrequently, then what "Teach" (

) suggests is all you need.

If you're doing it a lot (and the change from all caps to proper case is all you're doing) then you might be interested in this.

Rich (BB code):

Sub FormatToProper()
'Makes the entire sheet formatted as "Proper Case"
Application.ScreenUpdating = False
Dim Rng As Range
Dim c As Range
On Error Resume Next
'Set next line to any range you like
Set Rng = Cells.SpecialCells(xlCellTypeConstants, 2)
    
For Each c In Rng
    c.Value = Application.WorksheetFunction.Proper(c.Text)
Next c

Application.ScreenUpdating = True
End Sub

If you don't want every word capitalized, but just the first word in each cell, then maybe this...

Rich (BB code):

Sub FormatToSentence()
'Makes the entire sheet formatted in "Sentence case"
Application.ScreenUpdating = False
Dim Rng As Range
Dim c As Range
On Error Resume Next
'Set next line to any range you like
Set Rng = Cells.SpecialCells(xlCellTypeConstants, 2)
    
For Each c In Rng
    c.Value = UCase(Left(c.Text, 1)) & _
    LCase(Right(c.Text, Len(c.Text) - 1))
Next c

Application.ScreenUpdating = True
End Sub

A third option...(Gives you several choices of text case on the selected range. - Don't remember who wrote it, just that I like it.)

Rich (BB code):

Option Explicit
Sub TextCaseChange()
Dim RgText As Range
Dim oCell As Range
Dim Ans As String
Dim strTest As String
Dim sCap As Integer, _
    lCap As Integer, _
    i As Integer
Again:
Ans = Application.InputBox("[L]owercase" & vbCr & "ppercase" & vbCr & _
        "entence" & vbCr & "[T]itles" & vbCr & "[C]apsSmall", _
        "Type in a Letter", Type:=2)
If Ans = "False" Then Exit Sub
If InStr(1, "LUSTC", UCase(Ans), vbTextCompare) = 0 Or Len(Ans) > 1 Then GoTo Again
On Error GoTo NoText
If Selection.Count = 1 Then
    Set RgText = Selection
Else
    Set RgText = Selection.SpecialCells(xlCellTypeConstants, 2)
End If
On Error GoTo 0
For Each oCell In RgText
    Select Case UCase(Ans)
        Case "L": oCell = LCase(oCell.Text)
        Case "U": oCell = UCase(oCell.Text)
        Case "S": oCell = UCase(Left(oCell.Text, 1)) & _
            LCase(Right(oCell.Text, Len(oCell.Text) - 1))
        Case "T": oCell = Application.WorksheetFunction.Proper(oCell.Text)
        Case "C"
                lCap = oCell.Characters(1, 1).Font.Size
                sCap = Int(lCap * 0.85)
                'Small caps for everything.
                oCell.Font.Size = sCap
                oCell.Value = UCase(oCell.Text)
                strTest = oCell.Value
                'Large caps for 1st letter of words.
                strTest = Application.Proper(strTest)
                For i = 1 To Len(strTest)
                    If Mid(strTest, i, 1) = UCase(Mid(strTest, i, 1)) Then
                        oCell.Characters(i, 1).Font.Size = lCap
                    End If
                Next i
    End Select
Next
Exit Sub
NoText:
MsgBox "No Text in your selection @ " & Selection.Address
End Sub

And even a fourth option... download ASAP Utilities. This has (roughly) a million really useful things, changing text case being just one. (With even more choices than offered here!)

(Sorry you asked now? )
Dan

NateO · Mar 30, 2005

Hello,

Another option which handles multi-sentence strings (with varying punctuation):

Code:

Public Function sCase(ByRef strIn As String) As String
Dim bArr() As Byte, i As Long, i2 As Long
If strIn = vbNullString Then Exit Function
Let bArr = StrConv(strIn, vbFromUnicode)
Select Case bArr(0)
    Case 97 To 122
        bArr(0) = bArr(0) - 32
End Select
For i = 1 To UBound(bArr)
    Select Case bArr(i)
        Case 105
            If Not i = UBound(bArr) Then
                Select Case bArr(i + 1)
                    Case 32, 33, 39, 44, 46, 58, 59, 63, 148, 160
                        If bArr(i - 1) = 32 Then _
                            bArr(i) = bArr(i) - 32
                End Select
            ElseIf bArr(i - 1) = 32 Then _
                bArr(i) = bArr(i) - 32
            End If
        Case 33, 46, 58, 63
            For i2 = i + 1 To UBound(bArr)
                Select Case bArr(i2)
                    Case 97 To 122
                        bArr(i2) = bArr(i2) - 32
                        i = i2:   Exit For
                End Select
                Select Case bArr(i2)
                    Case 32, 33, 46, 63, 160
                    Case Else
                        i = i2:   Exit For
                End Select
            Next
    End Select
Next
sCase = StrConv(bArr, vbUnicode)
End Function
 
 
 
Sub Sentence_Case()
Dim cl As Range, clcMode As Long
 
With Application
    .ScreenUpdating = False
    clcMode = .Calculation
    .Calculation = xlCalculationManual
End With
 
On Error GoTo ErrorTrap
 
For Each cl In Range("a:a").SpecialCells(xlConstants, xlTextValues)
    cl.Value = sCase(LCase$(cl.Value))
Next cl
 
ErrorTrap:
 
With Application
    .Calculation = clcMode
    .ScreenUpdating = True
End With
End Sub

Change:

For Each cl In Range("a:a").SpecialCells(xlConstants, xlTextValues)

To the range in question.

This will also look at specific cases of the letter I, changing:

i'm, i, i'd, i'll

to

I'm, I, I'd, I'll

NateO · Jan 6, 2006

All right, when writing an algorithm, the trick is to make it right before you make it faster. Now that we have the desired functionality in place, our next step is to optimize.

StrConv() is a handy function, but it is an expensive function, and we don't need it in this case. While the resulting function might be a little harder to follow, as you only want to flip the bytes on every other element in the coerced byte array, the results might just be worth it, especially on smaller strings.

So, with this in mind, we make the following adjustments:

Public Function sCase(ByRef strIn As String) As String
Dim bArr() As Byte, i As Long, i2 As Long
If strIn = vbNullString Then Exit Function
Let bArr = strIn
Select Case bArr(0)
 Case 97 To 122
 bArr(0) = bArr(0) - 32
End Select
For i = 2 To UBound(bArr) Step 2
 Select Case bArr(i)
 Case 105
 If Not i = UBound(bArr) - 1 Then
 Select Case bArr(i + 2)
 Case 32, 33, 39, 44, 46, 58, 59, 63, 148, 160
 If bArr(i - 2) = 32 Then _
 bArr(i) = bArr(i) - 32
 End Select
 ElseIf bArr(i - 2) = 32 Then _
 bArr(i) = bArr(i) - 32
 End If
 Case 33, 46, 58, 63
 For i2 = i + 2 To UBound(bArr) Step 2
 Select Case bArr(i2)
 Case 97 To 122
 bArr(i2) = bArr(i2) - 32
 i = i2: Exit For
 End Select
 Select Case bArr(i2)
 Case 32, 33, 46, 63, 160
 Case Else
 i = i2: Exit For
 End Select
 Next
 End Select
Next
sCase = bArr
End Function

Sub testTime()
Debug.Print sCase(LCase$("hello? erm, i'M only testing, eh. indeed, " & _
 "i am inquisitve."))
Debug.Print sCase(LCase$("how old?! 22 Years."))
Debug.Print sCase(LCase$("how old?! twenty-two Years."))
Debug.Print sCase(LCase$("hmmmm.... wOrking?!?! sam i am. yes-no? " & _
 "isn't i'm isn't."))
Debug.Print sCase(LCase$("THE DAY WAS SUNNY AND I WORE A HAT.PETER WAS THERE "))
Debug.Print sCase(LCase$("no WorRies, i'm ONLY testIng! yes-no?"))
Debug.Print sCase(LCase$("mY fRiend & i"))
Debug.Print sCase(LCase$("iiiiiiiiiiiiii"))
Debug.Print sCase(LCase$("***T. toast %T i @"))
Debug.Print sCase(LCase$("re: sentences."))
Debug.Print sCase("hello? thought i'd test this for David McRitchie. NOTHING.")
End Sub

I haven't tested the code with a high-resolution timer, while I could, but on small strings I would imagine it's quite a bit faster than its predecessor (which was pretty fast to begin with).

How to remove caps and switch to lowercase

kerrysr

New Member

Excel Facts

PA HS Teacher

Well-known Member

HalfAce

MrExcel MVP

NateO

Legend

NateO

Legend

Similar threads

Forum statistics

Share this page

How to remove caps and switch to lowercase

kerrysr

New Member

Excel Facts

PA HS Teacher

Well-known Member

HalfAce

MrExcel MVP

NateO

Legend

NateO

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock