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
Pete_UK - 08 Mar 2007 13:05 GMT
No, a formula cannot affect the formatting - only a result.
Sorry,
Pete
> Hi,
>
[quoted text clipped - 13 lines]
>
> Jason
Don Guillett - 08 Mar 2007 13:16 GMT
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.

Signature
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
> Hi,
>
[quoted text clipped - 13 lines]
>
> Jason
Jay - 08 Mar 2007 13:35 GMT
That's not really an option Don as I have > 3000 rows. Oh well. Thanks for
confirming it anyway.
Rgds,
Jason
> 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
[quoted text clipped - 17 lines]
>>
>> Jason
Don Guillett - 08 Mar 2007 13:54 GMT
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.

Signature
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
> That's not really an option Don as I have > 3000 rows. Oh well. Thanks
> for confirming it anyway.
[quoted text clipped - 24 lines]
>>>
>>> Jason
Don Guillett - 08 Mar 2007 16:41 GMT
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

Signature
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
>
> IF? you are willing to convert to text, a macro can do it all.
[quoted text clipped - 29 lines]
>>>>
>>>> Jason
Roger Govier - 08 Mar 2007 15:48 GMT
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

Signature
Regards
Roger Govier
> That's not really an option Don as I have > 3000 rows. Oh well.
> Thanks for confirming it anyway.
[quoted text clipped - 25 lines]
>>>
>>> Jason