Proper Case

[Roger Bell]

I know that you can use the =Proper formula for changing case from all
Capitals to Sentence. Is there a way that you can change the case from
Capitals to Sentence for a range of cells?
Thanks for any help

 

[Don Guillett]

This may come in handy

Sub ChangeCase() 'Don Guillett
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub

 

[Bob Phillips]

Here is a UDF to do it

Private Function SentenceCase(ByVal para As String) As String
Dim oRegExp As Object
Dim oMatch As Object
Dim oAllMatches As Object

para = LCase(para)
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
oRegExp.Global = True
Set oAllMatches = oRegExp.Execute(para)
For Each oMatch In oAllMatches
With oMatch
Mid(para, .FirstIndex + 1 + .Length - 1, 1) = _
UCase(Mid(para, .FirstIndex + 1 + .Length - 1, 1))
End With
Next oMatch
SentenceCase = para
End Function


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gord Dibben]

Roger

Proper case is not sentence case.

This Is Proper Case.

Sentence case would have a capital at the beginning of each sentence only.

You want which?

Here is a macro for sentence case.

Sub optSentence_Click()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then Exit Sub
myStr = cel.Value
cel.Value = "=CapFirst(" & """" & myStr & """" & ")"
cel.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Next cel
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Here's one for Proper Case.

Sub optProper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord

 

[challa prabhu]

Hi,

No.

You cannot do this by entering the formula. You have to write a code.

Challa Prabhu

 

[PlayingToAudienceOfOne]

Sub Change_Case()
Dim ocell As Range
Dim Ans As String
Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")
If Ans = "" Then Exit Sub
For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
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)
End Select
Next

End Sub

 

[Nick Hodge]

Wow

Did you find every occurrence in the last 5 years!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Bob Phillips]

Certainly looks like it, and gave the same answer in most cases <g>

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nick Hodge]

I did find one with a function reply but other than that it was all the
same!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Gord Dibben]

Sentence Case must capitalize every first letter after a period, not just the
first letter of the sentence. The code posted fails to do that.

Try this one.

Sub optSentence_Click()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then Exit Sub
myStr = cel.Value
cel.Value = "=CapFirst(" & """" & myStr & """" & ")"
cel.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Next cel
End Sub

This Function goes along with it.

Function CapFirst(ByVal Str As String) As String
Dim aRegExp As Object, aMatch As Object, allMatches As Object
Set aRegExp = CreateObject("vbscript.regexp")
aRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
aRegExp.Global = True
Set allMatches = aRegExp.Execute(Str)
For Each aMatch In allMatches
With aMatch
Mid(Str, .firstindex + 1 + .Length - 1, 1) = _
UCase(Mid(Str, .firstindex + 1 + .Length - 1, 1))
End With
Next aMatch
CapFirst = Str
End Function


Gord Dibben MS Excel MVP

 

[Gord Dibben]

And his Sentence Case code is incorrect to start with.


Gord

 

[PlayingToAudienceOfOne]

3 years.

Wow

Did you find every occurrence in the last 5 years!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[PlayingToAudienceOfOne]

Make that "her" code.

And his Sentence Case code is incorrect to start with.


Gord

 

[Gord Dibben]

Excuse me.

I assume since you do not have a gender-based name you must get a lot of that.


Gord