Formatting part of the text into BOLD using macros

I have a string
<B>Osteonecrosis, Metastases to bone, Therapeutic response decreased</B>,
 Mobility decreased,
Renal cyst,
Uterine leiomyoma,
Thrombocytopenia,
Myalgia

I need to format the portion of text between <B> and </B> to Bold and the rest should be regular format.

Need to do this using macros since the data is being read from a TEXT File. I am unable to do that and whatever i am trying format the entire string to BOLD.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Do you need to handle multiple instances of bold within a single cell ?

Eg.

This is <b>bold</b> and so is <b>this</b>

Tim

 

 

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

To follow up on Tim's question... Did you want the <B> and </B> tags removed afterwards or should they remain (and if so, do they get bolded or not)? And are B's definitely always upper case?

*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi,

This just bolds the bits between the tags

Sub Bold_Bits()
TheStart = InStr(Range("a1"), ">") + 1
TheEnd = InStrRev(Range("a1"), "<")
With Range("a1").Characters(Start:=TheStart, Length:=TheEnd - TheStart).Font
        .FontStyle = "Bold"
    End With
End Sub


If this post answers your question, please mark it as the Answer.
Mike H
If this response answers your question then please mark as answer.

Mike H

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

On Thu, 22 Jul 2010 22:59:13 +0000, Axa1969 wrote:
 
>I have a string<B>Osteonecrosis, Metastases to bone, Therapeutic response decreased</B>,
> Mobility decreased,
>Renal cyst,
>Uterine leiomyoma,
>Thrombocytopenia,
>Myalgia
>
>
>I need to format the portion of text between <B> and </B> to Bold and the rest should be regular format.
>
>Need to do this using macros since the data is being read from a TEXT File. I am unable to do that and whatever i am trying format the entire string to BOLD.
 
The string needs to be in a cell, and you need to use the Characters
property to do that kind of formatting.
 
I am assuming you also want to remove the tokens <B> and </B>
 
Here's one method of doing that, and it will also handle multiple
"bolded substrings" within the string.  For example:
 
<B>Osteonecrosis, Metastases to bone, Therapeutic response
decreased</B>,
 Mobility decreased,
Renal cyst,
Uterine <B>leiomyoma</B>,
Thrombocytopenia,
Myalgia
 
 
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
 
To use this Macro (Sub), first select the cells you wish to process,
or change the code so it does it automatically.
 
<alt-F8> opens the macro dialog box. Select the macro by name, and
<RUN>.
 
============================================
Option Explicit
Sub BoldTextString()
  Dim s As String
  Dim r As Range, c As Range
  Dim re As Object, mc As Object, m As Object
  Dim i As Long
 
Set r = Selection
Set re = CreateObject("vbscript.regexp")
    re.Global = True
    re.ignorecase = True
   
For Each c In r
    re.Pattern = "<B>[\s\S]+?(?=</B>)"
        Set mc = re.Execute(c.Text)
    re.Pattern = "<B>|</B>"
    c.Value = re.Replace(c.Text, "")
    i = 0
    For Each m In mc
        c.Characters(m.firstindex + 1 - 7 * i, _
            m.Length - 3).Font.Bold = True
        i = i + 1
    Next m
Next c
End Sub
=================================
 
Ron

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

On Thu, 22 Jul 2010 22:59:13 +0000, Axa1969 wrote:
 
>I have a string<B>Osteonecrosis, Metastases to bone, Therapeutic response decreased</B>,
> Mobility decreased,
>Renal cyst,
>Uterine leiomyoma,
>Thrombocytopenia,
>Myalgia
>
>
>I need to format the portion of text between <B> and </B> to Bold and the rest should be regular format.
>
>Need to do this using macros since the data is being read from a TEXT File. I am unable to do that and whatever i am trying format the entire string to BOLD.
 
Although you didn't mention it, I've assumed you are importing this
TEXT file into Excel.  If that's not the case, please indicate which
office program you are using.
 
Ron

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 

A different approach from Ron's. This should also work if the final closing tag is missing.

Tim

 

Sub BoldIt(c As Range)

    Dim v As String, p1, i
    Dim hasTag As Boolean, inB As Boolean
   
    v = UCase(c.Value)
    i = 1
    p1 = 0
    Do While i < Len(v) - 4
        If Mid(v, i, 3) = "<B>" Then
            c.Characters(Start:=i, Length:=3).Delete
            v = UCase(c.Value)
            If Not inB Then p1 = i
            inB = True
            hasTag = True
        ElseIf Mid(v, i, 4) = "</B>" Then
            c.Characters(Start:=i, Length:=4).Delete
            v = UCase(c.Value)
            c.Characters(Start:=p1, Length:=i - p1).Font.Bold = True
            inB = False
        End If
        i = i + 1
    Loop
   
    If inB Then c.Characters(Start:=p1, Length:=Len(v) - p1 + 1).Font.Bold = True
   
End Sub

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

On Thu, 22 Jul 2010 23:53:00 +0000, Mike H.. wrote:
 
>
>
>Hi,
>
>This just bolds the bits between the tags
>
>Sub Bold_Bits()
>TheStart = InStr(Range("a1"), ">") + 1
>TheEnd = InStrRev(Range("a1"), "<")
>With Range("a1").Characters(Start:=TheStart, Length:=TheEnd - TheStart).Font
>        .FontStyle = "Bold"
>    End With
>End Sub
>If this post answers your question, please mark it as the Answer.
>Mike H
 
Note that it also assumes only a single pair of tags, and that there
are no other tags than those indicating Bold.
 
Ron

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

On Fri, 23 Jul 2010 00:12:41 +0000, RonRosenfeld <*** Email address is removed for privacy ***>
wrote:
 
Optimizing the regex expressions makes the code run faster:
 
============================
Option Explicit
Sub BoldTextString()
  Dim s As String
  Dim r As Range, c As Range
  Dim re As Object, mc As Object, m As Object
  Dim i As Long
 
Set r = Selection
Set re = CreateObject("vbscript.regexp")
    re.Global = True
    re.ignorecase = True
   
For Each c In r
    re.Pattern = "<B>[\s\S]+?</B>"
        Set mc = re.Execute(c.Text)
    re.Pattern = "</?B>"
    c.Value = re.Replace(c.Text, "")
    i = 0
    For Each m In mc
        c.Characters(m.firstindex + 1 - 7 * i, _
            m.Length - 3).Font.Bold = True
        i = i + 1
    Next m
Next c
End Sub
===========================
 
Ron

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Okay, I see everyone is giving you solutions without waiting for clarification on the questions you have been asked, so I might as well "join the party" as well. The following macro will make bold the text between the <B> and </B> tags, no matter how many tagged substrings of text are in anyone cell, for all the cells in cell Selection you make before running the macro...

Sub MakeSubTextBold()
  Dim X As Long, Position As Long, Cell As Range
  Dim Msg As String, Txt As String, Parts() As String
  For Each Cell In Selection
    Position = 0
    Txt = Cell.Value
    If InStr(Txt, "</B>") Then
      If UBound(Split(Txt, "<B>")) = UBound(Split(Txt, "</B>")) Then
        Txt = Replace(Txt, "</B>", "<B>")
        Parts = Split(Txt, "<B>")
        Cell.Font.Bold = False
        Cell.Value = Join(Parts, "")
        For X = 1 To UBound(Parts) Step 2
          Position = Position + Len(Parts(X - 1))
          Cell.Characters(Position, Len(Parts(X))).Font.Bold = True
          Position = Position + Len(Parts(X))
        Next
      Else
        Msg = Msg & Cell.Address(0, 0) & vbLf
      End If
    End If
  Next
  If Len(Msg) Then MsgBox "These cells had unbalanced tags and were not processed:" & _
              vbLf & "======================================" & vbLf & Msg, vbCritical
End Sub

Notes:
=========
1) All of the <B> and </B> tags will be remove from the text.

2) I placed some error checking into the code as well.

*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

You really don't have to reply to my questions any more as I posted a coded solution (with guess as to the answers) in a separate posting against your original message.
*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated June 3, 2020 Views 9,023 Applies to: