Changing Case

[Mark G.]

Is there a way to change the casing a text in Excel? Not necessarily all to
upper case, but maybe to where the first letter of a word is upper case? If
not a way, does anyone have a routine or macro that will do this please?
Thanks.

 

[pfsardella]

See UPPER, LOWER, and PROPER worksheet functions in Excel help.

See UCase and LCase functions in VBE help.

PROPER will capitalize the first letter of each word.

HTH
Paul

 

[Mark G.]

Found that, but how do I use or integrate this? Thanks.

 

[Leo Heuser]

This routine may get you started:

Sub LetterCase()
'Leo Heuseer, 24 Sept. 2003
Dim Cell As Range

For Each Cell In ActiveSheet.UsedRange
Cell.Value = _
Application.WorksheetFunction.Proper(Cell.Value)
Next Cell
End Sub


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

 

[Paul B]

Mark, you might also want to give this a try, you can put it in your
personal worksheet and then you can use it in all your workbooks, will bring
up an input box to let you select what to change to

Sub TextConvert()
'By Ivan F Moala
'will change the text that you have selected,
'if no text is selected it will change the whole sheet
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


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Ivan F Moala]

Another option...
Gives you all the options of Upper, Lower, Sentence, Title and smal
caps

something like.....


Code
-------------------

Option Explicit
'//
'// Amended code...thanks to Mike Leslie
'// 9th June 2003
'//

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

'// You need to select a Range to Alter 1st!

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