Text Formating

[jjkg100]

In formating text FORMAT > CELLS > FONT there is no Small Caps option. I
suppose you could type in all caps then use subscript or something, but that
seems rediculous. I looked at FORMAT > STYLE, there is no way to import
styles created in Word that I can see. When one is creating a document that
is to look consistant across your presentation, I do not see how Excel offers
that. Any ideas, short of copying back and forth which is unacceptable?

 

[Alan]

You can copy the data to a temporary location (like insert another
worksheet) in the form you want it by using
=LOWER(A1)
=UPPER(A1)
or
=PROPER(A1)
and copy over the whole data range, copy it to the temporary location then
right click the whole range, Copy, right click again, Paste Special > check
Values and overwrite the original data, then delete the temporary location,
(or sheet)
It only takes a few seconds,
Regards,

 

[Debra Dalgleish]

Excel's text formatting features are less robust than those in Word. You
could format in Excel, using what's available, then match that
formatting in Word.

Or, use a font in which lower case resembles small caps, such as
Copperplate Gothic

 

[Gord Dibben]

Sub Small_Caps()
Dim o As Object
Dim sCap As Integer, _
lCap As Integer, _
i As Integer
Dim testStr As String
Application.ScreenUpdating = False
For Each o In Selection
With o
If Application.IsText(.Value) Then
lCap = .Characters(1, 1).Font.Size
sCap = Int(lCap * 0.85)
'Small caps for everything.
.Font.Size = sCap
.Value = UCase(.Value)
testStr = .Value
'Large caps for 1st letter of words.
testStr = Application.Proper(testStr)
For i = 1 To Len(testStr)
If Mid(testStr, i, 1) = UCase(Mid(testStr, i, 1)) Then
.Characters(i, 1).Font.Size = lCap
End If
Next i
End If
End With
Next o
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

 

[jjgk100]

Alan,

Thank you for the suggestion. I was hoping not to have to go that route.
However, look at the post by Gord Dibben. Perhaps a one-time macro and on my
way. I remain hopefull that Excell will catch up with Word and PowerPoint as
far as formatting text.

Have a great week and thanks again,

John Karam

 

[jjgk100]

Hey Debra,

Thanks for the tip,

John Karam

Excel's text formatting features are less robust than those in Word. You
could format in Excel, using what's available, then match that
formatting in Word.

Or, use a font in which lower case resembles small caps, such as
Copperplate Gothic

 

[jjgk100]

Hi Gord,

Hmm I did a search but no luck. Thank you for finding and supplying the
link and code. Is it safe to say this is a macro that I can run on any given
cell? I would generally use this for titles and cell headings, just to match
our company's standard (unfortuantely, I set it without looking at Excel
first ).

Have a blessed week,

John Karam

 

[Gord Dibben]

Yes. It is a macro that you can run on any cell or set of selected cells.

To use it........

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

With your workbook open.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

I would place this and other useful macros into your Personal.xls file so's
it/they are available for all open workbooks.

Gord