Specify BOLD within a formula?

J

Jay

Hi,

I have a formula which looks up 2 text strings (at least part of them) and
concatenates them. Is there any way to have the second text string display
in BOLD by somehow specifying this within the formula?

=LEFT(VLOOKUP(B12,Derivative,4,FALSE),FIND("(",VLOOKUP(B12,Derivative,4,FALSE),1)-2)&"
-
"&LEFT(VLOOKUP(C12,Derivative,4,FALSE),FIND("(",VLOOKUP(C12,Derivative,4,FALSE),1)-2)

I want the second LEFT(VLOOKUP... to be in bold.

Any help greatly appreciated,

Rgds

Jason
 
D

Don Guillett

You can NOT do this in a FORMULA, only in a text string. So, if you want to
convert your formula to text, then just hilite the section desired and
format as desired.
 
J

Jay

That's not really an option Don as I have > 3000 rows. Oh well. Thanks for
confirming it anyway.

Rgds,

Jason
 
D

Don Guillett

IF? you are willing to convert to text, a macro can do it all.
Then, If you can find the start/stop of where you want to change the
format, again a macro using "characters" can do it for you with one mouse
click.
 
R

Roger Govier

Hi

The following piece of code should get you started on emboldening
everything after the hyphen between tour two concatenated strings.

I used cells J1:J3000 as the range to work on, change to suit the range
where your data lies

Sub BoldPartString()

Dim test As String, First As Integer, Last As Integer, Length As Integer
Dim c As Range
For Each c In Range("J1:J3000") '<== Change to suit your range
test = c.Value
If test = "" Then Exit Sub
Last = Len(c)
First = WorksheetFunction.Find("-", c) + 1
Length = Last - First + 1
' now embolden from first to last characters
With c.Characters(Start:=First, Length:=Length).Font
.FontStyle = "Bold"
End With
First = 0: Last = 0: Length = 0
Next c
End Sub


You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

David McRitchie has lots of useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

Don Guillett

All at once for col D

Sub maketextandbold()
For Each c In range("D2:D" & _
Cells(Rows.Count, "D").End(xlUp).Row)
With c
.Value = .Value 'converts from formula
x = InStr(c, "-")
.Characters(x, Len(.Value) - x + 1). _
Font.FontStyle = "bold"
End With
Next
End Sub
 
Top