MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Capitalizing Letters

Posted by Glenn Balian on January 22, 2002 1:17 PM

In EXCEL is there a quick way to turn small caps into all caps as in "Word" Document. (e.g. xx to XX)

Posted by on January 22, 2002 1:26 PM

=Ucase(text) (NT)

Posted by Mark on January 22, 2002 1:53 PM

G'day Glen,

In Access VBA we use this to Capitalize the first leteer of each word entered into the field or in this case cell.

I'm not %100 sure if this will work in Excel, but it may get you started.

Function AutoCap(sInput As String) As String

Dim x As Integer
Dim sWork As String

sWork = Trim(sInput)
Mid(sWork, 1, 1) = UCase(Left(sWork, 1))

For x = 1 To Len(sWork) - 1
If Mid(sWork, x, 1) = " " Then Mid(sWork, x + 1, 1) = UCase(Mid(sWork, x + 1, 1))
Next x

AutoCap = sWork

End Function



Posted by Mark W. on January 22, 2002 2:29 PM

UPPER() ? [NT]

Posted by Joe Was on January 22, 2002 2:36 PM

This code go's in the sheet module, it will turn any lower case in column B & C.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCells As Range
Application.EnableEvents = False
If Not Intersect(Target, Columns("B:C")) Is Nothing Then
For Each rCells In Intersect(Target, Columns("B:C"))
rCells = UCase(rCells.Text)
End If
Application.EnableEvents = True
End Sub

It runs automatically from the sheet module, hope this helps. JSW