Small Caps Font in Excel 1

[Hmadyson]

There is a small caps font in Word which will make your first letter a larger cap then the rest of your upper cap text. Is there any way to get this font in Excel? Seems that if they put a feature in Word, then it should also be in Excel.

 

[Wray69]

there is a function in excel you can use =Proper(cell reference) this will make the first letter of every word in the cell that you reference capitalized.

Regards -

Wray

 

[Nikita6003]

Hiya,

don't think there is a SmallCaps option in Excel, but you *can* cheat:
- enter the text in caps
- Edit the cel: select only the text you want to appear as the smaller caps
Press [Ctrl-1] - this'll open the Format dialog box
Select a smaller fontsize - this size'll only apply to the selected part of the text

HTH

Cheers
Nikki

 

[Hmadyson]

I wrote a macro to do this, assuming that the font size difference is 3 sizes, if anyone wants me to post it, I can.

 

[Wray69]

Opps!!! I mis-read your original post... sorry about that

Wray

 

[lespaul]

Wow Nikki! I have tried making different fonts within a cell before and thought it couldn't be done! Your ctrl-1 is a sweet treat! Thanks for the posting!

Stars to you! Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[bandit600]

Hmadyson,

I would be interested if you would like to post your macro.

Have you thought of assigning it to a custom button on your formatting toolbar?


bandit600

 

[Hmadyson]

This is the first time that I have needed to use this macro, so it is kind of raw, but it did what I needed it to do.

Sub MakeThisCellSpecialFormat()
Dim FontSize As Integer
Dim i As Integer

With ActiveCell
.Value = UCase(.Value)
FontSize = .Font.Size
i = 0
Do
ActiveCell.Characters(Start:=i + 1, Length:=1).Font.Size = FontSize + 3
i = InStr(i + 1, .Value, " &quot
Loop While i > 0
End With
End Sub

 

[bandit600]

Hmadyson,

Keeping to similar lines, but doing away with the loop, you could try

Sub MakeThisCellSpecialFormat()
Dim FontSize As Integer

With ActiveCell
.Value = UCase(.Value)
FontSize = .Font.Size
ActiveCell.Characters(Start:=2).Font.Size = FontSize - 3
End With

End Sub

Would it only be a few cells that would need this formatting?

bandit600

 

[Hmadyson]

but this would not make each word proper case, this would only make the entire cell proper case, so no getting rid of the loop.

 

[Nikita6003]

Actually, the Characters property in Bandit's example leaves the first letter the original size + can be used incombination with Instr and Mid to separate out each word.

But if you want each letter of a new word to start with a large cap, you'd be better off with your loop - makes for easier-to-read & neater code ;-)

Cheers
Nikki

 

[greenheys]

I've done similar to the above, loop around etc...however....

If you have a string of numbers in there, this makes the first number bigger, but not the rest...which looks a bit silly, so I've put in an exception so that all numbers go fontsize +3.

The real problem seems to be that if you do this it wipes out any formula you have in the cell.

Anyone have any idea how to preserve the formula and put in small caps?

Thanks,

Greg