Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2007

Tip: Looking for answers? Try searching our database.

Specify BOLD within a formula?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jay - 08 Mar 2007 12:03 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.