How to embed font/size changes within Excel formula

[Guest]

I want to change from a large font size of the first word in a cell to a
smaller font size for the remainder of words, for cel text that is built up
multiple text cells via a formula. This type of formatting is quite easy for
a text (not formula) cell (just select the portion of text you want different
and change font, size, color, etc.). However, I haven't been able to find
the set of operators/modifiers to accomplish the same thing within a formula.

Please help. Thanks

 

[T. Valko]

Not possible using a formula.

--
Biff
Microsoft Excel MVP


"Tired of poor table nagivation"

 

[Bernard Liengme]

I am unaware of any way to do this.
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Tired of poor table nagivation"

 

[Roger Govier]

Hi

You would need some VBA code to do this.
The following code needs to be copied into a standard module in your
Workbook.

DO THIS ON A COPY OF YOUR WORKBOOK FIRST - just in case!!!!

Press Alt+F11 to start the Visual Basic Editor.
Click Insert>Module
In the new module created, copy the code as below and paste into the white
pane.
Press Alt+F11 to go back to your worksheet

On your sheet Press Alt+F8 to bring up macros>highlight BoldFirstRow>Run

This has been set to change the font for the first word to Bold Font 12, and
the remainder to Regular font 8
It has been set to start from row 10, of Column E (5). Change the values to
suit the range where you want this to take place.

NOTE: This will change the values in the cells from a formula to a value.

Sub BoldFirstWord()
Dim firstspace As Long, wordlength As Long
Dim len1 As Long, start2 As Long, len2 As Long
Dim cell As Range
Dim row As Long, lastrow As Long, colno As Long, n as long

row = 10 '<--- set to row number for start
colno = 5 '<--- set to column number to use
lastrow = Cells(Rows.Count, colno).End(xlUp).row
For n = row To lastrow
Cells(row, colno).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

firstspace = WorksheetFunction.Find(" ", ActiveCell)
wordlength = Len(ActiveCell.Value)
len1 = firstspace - 1
start2 = firstspace + 1
len2 = wordlength - firstspace

With Selection.Characters(Start:=1, Length:=len1).Font
.Name = "Arial" '<--- set as required
.FontStyle = "Bold" '<--- set as required
.Size = 12 '<--- set as required
End With

With Selection.Characters(Start:=start2, Length:=len2).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With

Next n

End Sub

--
Regards
Roger Govier



"Tired of poor table nagivation"

 

[JE McGimpsey]

You can't do that with a formula. You'd have to replace the formula with
a _Calculate event macro that would do the calculation, insert the
result, then format the cell.

Tired of poor table nagivation