Split text with <div> <b> and/or <p>

[twelin]

Hi,
I have hundreds of texts in Excel that i need to split by their "html splits". Problem is that all methods are used and sometimes there are multiple breaks.

Example 1:
The fund is actively managed, whereby the fund manager takes active, independent investment decisions. The objective is to exceed the returns in the underlying market (defined as the benchmark index) over the long term. <div><br><div>The fund invests in interest-bearing securities denominated in euro. The fund's assets shall be invested in interest-bearing securities issued or guaranteed by a state, municipality or by a government or municipal authority or by some intergovernmental agency in which one or several states are members, as well as in covered bonds. The average residual fixed interest duration is between two and ten years.<br></div><div><br></div><div>These shall have a credit rating of no less than BBB- (investment grade) according to Standard & Poor's or Baa3 according to Moody's rating scale. The fund may also invest in securities that lack an official credit rating if we consider it as having an equivalent credit quality.</div></div>

Example 2:
<p>The fund is actively managed, whereby the fund manager takes active, independent investment decisions. The objective is to exceed the returns in the underlying market (defined as the benchmark index) over the long term.</p><div>The fund invests in equities that are primarily issued by companies in Asia, New Zealand and Australia or in companies that have a minimum of 50% of their business activity or market in Asia, and thereby have a currency exposure in these markets.<br><div><br></div><div>The thematic investment strategy implies that investments occur primarily in companies associated with long-term investment themes that have been carefully selected, analysed and are expected to experience a long period of growth.<br></div>

Could anyone come up with a VBA to solve this? My goal is to split by alla "breaks" (div, br and div)

 

[twelin]

That hardly counts for ..

However, see if this works for you. Assumes data in A2:Ax with results in columns B, C, D. ...

Sub Split_Text()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(<.*?>)+"
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    a(i, 1) = RX.Replace("<>" & a(i, 1), "|")
  Next i
  With Range("B2").Resize(UBound(a))
    .Value = a
    .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(1, 9)
  End With
End Sub

Wonderful! Works like a charm, thank you!

 

[Peter_SSs]

You're welcome. Glad it worked for you.

 

[Peter_SSs]

Actually, my code may leave stray spaces before/after some of the results. If you want that tidied up, change the Pattern line to this

RX.Pattern = " *(<.*?>)+ *"