MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Small caps font

Posted by Marvin on April 25, 2001 5:00 PM

Is there a way to format text text input as caps and lower case letters to display large and small caps like I can in Word? I seem to be able to find solutions for every MS Office app except Excle.

Posted by Mark W. on April 25, 2001 5:44 PM

Like using the PROPER() function?

Posted by Kevin James on April 25, 2001 6:33 PM


As Mark already indicated, you could use the PROPER function. This will give you small caps only if you set the font for the whole spreadsheet (or the whole range of text cells) to that type font.

Personally, I don't have that font installed. If you are preparing something for others to use, here is something to think about: Analysts have commented that the general populous finds all upper case presentations, printed or online, to be an eye strain to read.

Posted by Dave Hawley on April 25, 2001 6:42 PM

Hi Marvin

To have Excel Proper text automatically you could use the Sheet_Change event. Right click on your sheet name tab and select "View Code" and paste in this code;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
Target = WorksheetFunction.Proper(Target)
End Sub

This will Proper ALL text you type.


OzGrid Business Applications

Posted by Tim Francis-Wright on April 26, 2001 1:35 PM

There are some fonts that are set up with small
capitals as the default small letters. If you're
using a more universal font, you can fake small
caps with the following macro that works on the
currently selected text:

Sub SmallCaps()
' fakes the Small Caps from Word
' Only issue: if first character is small,
' Excel decreases the default font size for
' that cell.

Dim cel As Range
Dim CellLength%, i%, DefaultSize

For Each cel In ActiveWindow.Selection
CellLength = cel.Characters.Count
DefaultSize = cel.Font.Size
For i = 1 To CellLength
With cel.Characters(i, 1)
If .Text = UCase(.Text) Then
.Font.Size = DefaultSize
.Text = UCase(.Text)
.Font.Size = DefaultSize - 2
End If
End With
Next i
Next cel

Set cel = Nothing

End Sub

Hope this helps!