How to embed font/size changes within Excel formula
 

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

Answer: How to embed font/size changes within Excel formula
 

Unfortunately, it is not possible to embed font/size changes within an Excel formula. Formulas are used to calculate values and cannot contain formatting instructions. However, there are a few workarounds that you can use to achieve a similar result.

One option is to use the CONCATENATE function to combine the text from multiple cells into one cell, and then apply the formatting to the combined text. Here's how you can do it:

1. In a new cell, enter the CONCATENATE function, followed by the cell references of the cells you want to combine. For example, if you want to combine the text from cells A1, B1, and C1, you would enter the following formula:
   Code:

   ---

   =CONCATENATE(A1,B1,C1)

   ---

2. Once you have the combined text in the new cell, you can select the portion of text that you want to format differently and change the font, size, color, etc.

Another option is to use conditional formatting to apply different formatting to different parts of the text based on certain criteria. For example, you could use conditional formatting to apply a larger font size to the first word in a cell and a smaller font size to the rest of the words. Here's how you can do it:

1. Select the cell or range of cells that you want to format.
   
2. Click on the "Conditional Formatting" button in the "Home" tab of the ribbon.
   
3. Select "New Rule" from the dropdown menu.
   
4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
   
5. In the "Format values where this formula is true" field, enter a formula that will identify the first word in the cell. For example, if the first word is always separated from the rest of the text by a space, you could use the following formula:
   Code:

   ---

   =LEFT(A1,FIND(" ",A1)-1)=A1

   ---

6. Click on the "Format" button and select the font size and any other formatting options that you want to apply to the first word.
   
7. Click "OK" to close the "Format Cells" dialog box.
   
8. Click "OK" again to close the "New Formatting Rule" dialog box.
   
9. Repeat steps 3-8, but this time enter a formula that will identify the rest of the text in the cell. For example, you could use the following formula:
   Code:

   ---

   =LEFT(A1,FIND(" ",A1)-1)<A1

   ---

10. Click on the "Format" button and select the font size and any other formatting options that you want to apply to the rest of the text.
    
11. Click "OK" to close the "Format Cells" dialog box.

Now, the first word in each cell will be formatted with the larger font size, and the rest of the text will be formatted with the smaller font size.

How to embed font/size changes within Excel formula
 

Not possible using a formula.

--
Biff
Microsoft Excel MVP


"Tired of poor table nagivation"
. com wrote in message
...
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

How to embed font/size changes within Excel formula
 

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"
. com wrote in message
...
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

How to embed font/size changes within Excel formula
 

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 InsertModule
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 macroshighlight BoldFirstRowRun

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"
. com wrote in message
...
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

How to embed font/size changes within Excel formula
 

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.

In article ,
Tired of poor table nagivation
. com wrote:

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

That should read Cells(n, colno).Select

-- it was only applying the formatting on the first row, instead of the
entire column.

-Richie