Solved Bold text in a cell using a formula

Microsoft Excel 2007
December 28, 2009 at 13:45:43
Specs: Windows Vista
I want to know if there is a way to bold parts of text with in a cell using a formula?

Using this in a cell:
="___Hello "&C5&" how are you?"

This will display as:
___Hello Wayne how are you?

when I want it to be:
___Hello Wayne how are you?

where the cell C5 is identified, I want to make the contents displayed as Bold. Can anyone help me?

Thank you,
Wayne


See More: Bold text in a cell using a formula

Report •


✔ Best Answer
December 29, 2009 at 06:55:53
Hi,

Here is a way to bold a list of words inside a longer phrase.

It requires visual basic and it uses the Change event.

For this example three words will be included in the new phrase and will be Bolded.
Put three words in cells E4, E5 and E6:

	E
4	Wayne
5	John
6	big

Put this formula in cell E8:
="Films with "&E6&" film actor "&E5&" "&E4

Now right click on the Sheet name tab at the bottom of the Excel window. This example used Sheet1
Select View Code
In the window that opens enter this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objISect As Range

'this range object is created if the changed range (Target) 
'contains any part of range E4:E6
Set objISect = Intersect(Target, Range("E4:E6"))
'make the changes if the range object has been set (i.e. is Not Nothing)
If Not objISect Is Nothing Then
    'create the new string in cell E9
    Range("E9").Value = Range("E8").Text
    'remove any existing Bold font
    Range("E9").Font.Bold = False
    'now bold each of the words from the list E4:E6
    Range("E9").Characters(InStr(1, Range("E9").Text, Range("E4").Text), _
                        Len(Range("E4").Text)).Font.Bold = True
    Range("E9").Characters(InStr(1, Range("E9").Text, Range("E5").Text), _
                        Len(Range("E5").Text)).Font.Bold = True
    Range("E9").Characters(InStr(1, Range("E9").Text, Range("E6").Text), _
                        Len(Range("E6").Text)).Font.Bold = True
End If
End Sub

To use more words, change the range in the line starting Set objIsect
For a fourth word the line becomes:Set objISect = Intersect(Target, Range("E4:E7"))
and add a new line under the heading 'now bold each of the words from the list E4:E6
Note that there are three lines of code, but each has been split onto two visual lines using the '_' character.
Just copy the last two visual lines, and paste them before End if, and change the E6 to E7 for a fourth word.

When you change any word in your list a new phrase with bolded text appears in cell E9.

Note that when you move cells referenced in this code, the code does not change, so you will have to change the code to show the new cell addresses.

Regards



#1
December 28, 2009 at 19:47:43
There are no built functions that will change the format of a single word within a cell. You would need to use VBA.

While a macro could be written to meet the exact requirements stated in your post, it would probably be better if you were more specific in what you need. That way any work that we do wouldn't need to be re-done once you tell us what you are really trying to do.


Report •

#2
December 29, 2009 at 05:24:07
I am actually trying to do what I stated in the original post.

I want to concatenate many cells together and where the cell is referenced in the string I put together, I what them to display as bold text.


Report •

#3
December 29, 2009 at 06:50:53
Here is the problem as I see it. Maybe if I explain what I'm thinking, someone else can come up with a different method.

There are 2 ways that I know of to bold specific characters within a cell:

1 - Within Excel, select the cell, select the specific characters in the formula bar and click the Bold icon.

2 - Using the VBA Characters method.

This code will bold the Wayne in A1, after placing the string "___Hello Wayne how are you?" in the cell.

With Worksheets("Sheet1").Range("A1")
    .Value = "___Hello Wayne how are you?"
    .Characters(10, 5).Font.Bold = True
End With

The problem with both of these methods is that "Wayne" really isn't in the cell when you use the Concatenation function. Instead, you have a reference to C5.

You can't select Wayne from within Excel since it will not appear in the formula bar when you select the cell.

You cannot specific the Character location for Wayne within VBA since the cell contains a formula, not a text string.

The only way I can think of to accomplish your goal is to use VBA to replace the formula with the actual string and then use the Characters method.

Let's say your Concatenation formula is in A1.

This code will replace the formula with the string and bold the portion that matches what is in C5. Keep in mind that once you run this code, the formula will be gone and changing C5 will have no impact on A1.

I suggest you try this in a backup copy of your spreadsheet. In the meantime, I'll keep looking for a way to do this without replacing the formula.

Sub BoldSomeText()
'Replace the formula with the full string
   Range("A1") = Range("A1").Value
'Find the starting position of the string in C5
'within the string produced by the formula
   myPos = InStr(1, Range("A1"), Range("C5"), 1)
'Bold the string from C5
     Range("A1").Characters(myPos, Len(Range("C5"))).Font.Bold = True
End Sub


Report •

Related Solutions

#4
December 29, 2009 at 06:55:53
✔ Best Answer
Hi,

Here is a way to bold a list of words inside a longer phrase.

It requires visual basic and it uses the Change event.

For this example three words will be included in the new phrase and will be Bolded.
Put three words in cells E4, E5 and E6:

	E
4	Wayne
5	John
6	big

Put this formula in cell E8:
="Films with "&E6&" film actor "&E5&" "&E4

Now right click on the Sheet name tab at the bottom of the Excel window. This example used Sheet1
Select View Code
In the window that opens enter this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objISect As Range

'this range object is created if the changed range (Target) 
'contains any part of range E4:E6
Set objISect = Intersect(Target, Range("E4:E6"))
'make the changes if the range object has been set (i.e. is Not Nothing)
If Not objISect Is Nothing Then
    'create the new string in cell E9
    Range("E9").Value = Range("E8").Text
    'remove any existing Bold font
    Range("E9").Font.Bold = False
    'now bold each of the words from the list E4:E6
    Range("E9").Characters(InStr(1, Range("E9").Text, Range("E4").Text), _
                        Len(Range("E4").Text)).Font.Bold = True
    Range("E9").Characters(InStr(1, Range("E9").Text, Range("E5").Text), _
                        Len(Range("E5").Text)).Font.Bold = True
    Range("E9").Characters(InStr(1, Range("E9").Text, Range("E6").Text), _
                        Len(Range("E6").Text)).Font.Bold = True
End If
End Sub

To use more words, change the range in the line starting Set objIsect
For a fourth word the line becomes:Set objISect = Intersect(Target, Range("E4:E7"))
and add a new line under the heading 'now bold each of the words from the list E4:E6
Note that there are three lines of code, but each has been split onto two visual lines using the '_' character.
Just copy the last two visual lines, and paste them before End if, and change the E6 to E7 for a fourth word.

When you change any word in your list a new phrase with bolded text appears in cell E9.

Note that when you move cells referenced in this code, the code does not change, so you will have to change the code to show the new cell addresses.

Regards


Report •

#5
December 29, 2009 at 07:40:37
Humar,

If I correctly understand what you are doing, you are retaining the original formula by placing the "value" of that formula in a different cell and then using the Characters method.

This overcomes the fact that the Characters method won't work on a cell containing a formula.

It's an interesting workaround that just might fit the OP needs.


Report •

#6
December 29, 2009 at 07:51:34
Hi DerbyDad03,

Yes, I am copying the Text of the concatenated cell into another cell so that I can use the characters method.

I tried characters on the cell with the formula, but it doesn't work, as you pointed out.

Once the text has been copied to another cell, Characters works fine.

Using the Change event lets the user change the words in the phrase and get the new phrase with the concatenated words in bold.

The user can move the original cell (E8) in my example, out of sight such as cell AA8, or into a hidden column or even to another sheet, although they will have to change the address in the code.

Regards


Report •


Ask Question