Formatting Data Extraction with added Delimeter with Excel VBA

Question

Currently I have an excel macro that when run, it goes down cell by cell in a column, extracting only the bold words from a string that's a paragraph long. It places all the bold words found in the string in the adjacent cell. For example...cell A1 is a paragraph of text, with some words bolded, when the macro is run, all bolded words found in A1 are then placed in cell B1. It works great except that all the bolded words are placed in one long string, with no spaces in between each bolded word. I need help creating a delimiter to be placed in between all the bolded words found in the string. Thanks!

Sub GetBoldedInfo()
    Dim txt As String
    boldedRow = Cells(Rows.Count, "A").End(xlUp).Row

    For Each c In Range(ActiveCell, ActiveCell.End(xlDown))
        myboldtext = ""
    For i = 1 To Len(c.Value)
        If c.Characters(Start:=i, Length:=1).Font.FontStyle = "Bold" Then
            txt = Mid(c.Value, i, 1)
    'text in bold
            myboldtext = myboldtext & txt
        End If
    Next
    c.Offset(0, 1) = myboldtext
    Next
End Sub

B1 Output:

 China – ABC:NIKEAccount # 1234567890RetailFreight - Ocean1 potential annual shipmentsannual revenue of US $1

Ideal B1 Output:

  China – ABC:;NIKE;Account # 1234567890;Retail;Freight - Ocean;1 potential annual shipment;annual revenue of US $1

Really Ideal Output: Delimited by cell in adjacent columns.

  B1 = China – ABC: C1 = NIKE D1 = Account  # 1234567890 etc.

Answer 1

Try something like this.

Replace

    If c.Characters(Start:=i, Length:=1).Font.FontStyle = "Bold" Then
        txt = Mid(c.Value, i, 1)
'text in bold
        myboldtext = myboldtext & txt
    End If

With

If c.Characters(Start:=i, Length:=1).Font.FontStyle = "Bold" Then
        If c.Characters(Start:=i + 1, Length:=1).Font.FontStyle <> "Bold" Then
'text in bold with delimiter
            txt = Mid(c.Value, i, 1) 
            myboldtext = myboldtext & txt & ";"
        else 
'text in bold
            txt = Mid(c.Value, i, 1)
            myboldtext = myboldtext & txt
        End If
    End If

This should automatically append a delimiter when the character after the bold character is not bold.

• code not tested. provided to demonstrate the idea.